topic Re: How to mimic a query's constraints in DAX in DAX Commands and Tips

How to mimic a query's constraints in DAX

Anonymous — Thu, 27 May 2021 14:21:43 GMT

Hi all,

I am connected to a live database in PBI via a Dataflow. I use a simple query to give me a count of what I am looking for and need to replicate that in DAX.

Query:

SELECT count(IPID) as theSwitches 
FROM KUB.MVIEW_E_SWITCH 
WHERE (NORMALPOSITIONA = 0 OR NORMALPOSITIONB = 0 OR NORMALPOSITIONC = 0) 
AND (FEEDERID <> FEEDERID2) 
AND FEEDERID2 is not null

Based on my DAX statement now, I just have it flagged TRUE or FALSE and am just counting the TRUE values in my report section, but it doesn't have to be formatted this way. I cannot get the number of true values to replicate the values from the query when I run it in SQL Developer. Here is my current DAX:

TIE= (MVIEW_E_SWITCH[NORMALPOSITIONA]= 0 || 
MVIEW_E_SWITCH[NORMALPOSITIONB] = 0 || 
MVIEW_E_SWITCH[NORMALPOSITIONC]= 0) 
&& (MVIEW_E_SWITCH[FEEDERID] <> MVIEW_E_SWITCH[FEEDERID2]) 
&& NOT(ISBLANK(MVIEW_E_SWITCH[FEEDERID2]))

Can anyone help me understand why the count of the two are different?

Re: How to mimic a query's constraints in DAX

SivaMani — Thu, 27 May 2021 14:40:51 GMT

Anonymous,

Try this DAX code in a measure,

Count = CALCULATE ( COUNT ( MVIEW_E_SWITCH[IPID] ), FILTER ( MVIEW_E_SWITCH, ( MVIEW_E_SWITCH[NORMALPOSITIONA] = 0 || MVIEW_E_SWITCH[NORMALPOSITIONB] = 0 || MVIEW_E_SWITCH[NORMALPOSITIONC] = 0 ) && MVIEW_E_SWITCH[FEEDERID] <> MVIEW_E_SWITCH[FEEDERID2 ] && NOT ISBLANK ( MVIEW_E_SWITCH[FEEDERID2] ) ) )

Re: How to mimic a query's constraints in DAX

Anonymous — Thu, 27 May 2021 14:56:28 GMT

Thanks for the input Siva! I am getting the same number I was getting when I did a count of all "True" values in my custom column. When I run my query I get a count of 991, but when I do this count i am getting 1442, which is a pretty big difference and I cannot seem to figure out what is wrong with the DAX... Would M Code be better to use for something like that?

Re: How to mimic a query's constraints in DAX

SivaMani — Thu, 27 May 2021 15:32:35 GMT

Anonymous,

It should work mostly until unless there is a gap between your database and the Power BI dataset. Can you check that?

Re: How to mimic a query's constraints in DAX

Anonymous — Thu, 27 May 2021 16:16:33 GMT

There does not seem to be a gap. There are 4,947 rows in both the PowerBI dataset and database.

Re: How to mimic a query's constraints in DAX

SivaMani — Fri, 28 May 2021 03:22:34 GMT

Anonymous,

If possible, remove sensitive information and Share the pbix file for further analysis.