How to mimic a query's constraints in DAX

[Anonymous]

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? 

[SivaMani]

@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] )
    )
)

[Anonymous]

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?

[SivaMani]

@Anonymous,

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

[Anonymous]

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

[SivaMani]

@Anonymous,

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