Need help with expression formula based on power bi dataset to validate trigger

[fcucek]

Hello, 

I have just little experience with power automate expressions and need your help, after my own research failed. 

My Flow's trigger is a data driven alert (works fine) followed up by "run a query against dataset" step with the extracted dax query from the performance analyzer (see below)

The "Trigger True" part should now include an expression where the distinct count of the DataManagementExecutionJobDetailStaging'[TARGETSTATUS] = 1 

The Idea behind is, that I want to move forward in this flow just if the target status is the same for every row. If in any row I will have a different status (distinctcount > 1) then the next steps of the flow shouldn't be executed, and that's why i'm searching for explicitly this combination (distinctcount = 1)

Can anyone help me with that? 

// DAX Query
DEFINE
	VAR __DS0Core = 
		SUMMARIZE(
			'DataManagementExecutionJobDetailStaging',
			'DataManagementExecutionJobDetailStaging'[ENTITYNAME],
			'DataManagementExecutionJobDetailStaging'[TARGETENDDATETIME],
			'DataManagementExecutionJobDetailStaging'[TARGETSTATUS],
			'DataManagementExecutionJobDetailStaging'[DEFINITIONGROUPID],
			'DataManagementExecutionJobDetailStaging'[JOBID],
			'DataManagementExecutionJobDetailStaging'[TARGETRECORDSCREATEDCOUNT]
		)

	VAR __DS0PrimaryWindowed = 
		TOPN(
			501,
			__DS0Core,
			'DataManagementExecutionJobDetailStaging'[TARGETENDDATETIME],
			1,
			'DataManagementExecutionJobDetailStaging'[ENTITYNAME],
			1,
			'DataManagementExecutionJobDetailStaging'[TARGETSTATUS],
			1,
			'DataManagementExecutionJobDetailStaging'[DEFINITIONGROUPID],
			1,
			'DataManagementExecutionJobDetailStaging'[JOBID],
			1,
			'DataManagementExecutionJobDetailStaging'[TARGETRECORDSCREATEDCOUNT],
			1
		)

EVALUATE
	__DS0PrimaryWindowed

ORDER BY
	'DataManagementExecutionJobDetailStaging'[TARGETENDDATETIME],
	'DataManagementExecutionJobDetailStaging'[ENTITYNAME],
	'DataManagementExecutionJobDetailStaging'[TARGETSTATUS],
	'DataManagementExecutionJobDetailStaging'[DEFINITIONGROUPID],
	'DataManagementExecutionJobDetailStaging'[JOBID],
	'DataManagementExecutionJobDetailStaging'[TARGETRECORDSCREATEDCOUNT]

[lbendlin]

What's your question? How to add the count column?

By the way the DAX can be simplified

// DAX Query
EVALUATE SUMMARIZE(
			'DataManagementExecutionJobDetailStaging',
			[ENTITYNAME],
			[TARGETENDDATETIME],
			[TARGETSTATUS],
			[DEFINITIONGROUPID],
			[JOBID],
			[TARGETRECORDSCREATEDCOUNT]
		)

[fcucek]

I need help with the expression for the condition. How can I write this expression inside "true condition" , based on what I described earlier?

[lbendlin]

Run a separate DAX query that specifically returns that result.

EVALUATE {COUNTROWS(VALUES('DataManagementExecutionJobDetailStaging'[TARGETSTATUS]))}

[fcucek · Accepted solution]

Thanks for the idea. With that and this post, I was able to solve my problem. 

[lbendlin]

If you like you can also eliminate the Parse JSON and Compose 1 steps and query the Power BI results directly as they are coming in as JSON already.

[fcucek]

the power bi dataset query returns a table with one row and a 1 or 2 as number inside this row (see dax below) and i wasn't able to use any of those output options here (image below) to set up the trigger. I tried with row/ first table rows / body each equals 1 and also contains 1 and it didn't work so far... 

// DAX Query
EVALUATE
	ROW(
		"DistinctCountTARGETSTATUS", CALCULATE(DISTINCTCOUNT('DataManagementExecutionJobDetailStaging'[TARGETSTATUS]))
	)

I tried also some functions, but without success. maybe I missed something in between 😅 

[lbendlin]

The simplest possible return value is

// DAX Query
EVALUATE {DISTINCTCOUNT('DataManagementExecutionJobDetailStaging'[TARGETSTATUS])}

You can then grab that with

outputs('Run_a_query_against_a_dataset')?['body/firstTableRows'][0]['[Value]']

.

[fcucek · Accepted solution]

do mean this way?

or just to paste it in the function editor?

[lbendlin]

no, use the complete formula directly in your comparison.  Use Dynamic content, not the Function section.

[fcucek]

ok now i have this:

my trigger is set up to work just from tuesday to saturday, so i will be able to test productively again on tuesday, but i really appreciate your help and will keep you posted about the results. looks promising ... 😃

[fcucek]

thank you @lbendlin , I can confirm now that it works fine. Great!