DAX Statement to Replicate Query Constraints

0
votes

I have created a dashboard in PBI using a dataflow from a live database. I am trying to replicate the constraints of a query in DAX and am a little lost on how to get the correct DAX syntax/results.

Below is the 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

Here is my 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]))

Here is some dummy data:

IPID     NORMALPOSITIONA   NORMALPOSITIONB   NORMALPOSITIONC   FEEDERID    FEEDERID2
123141          1                 1                1             GC21        GC12
145361          0                 0                1              
096842          0                 0                0             BC21        BC32
053912          0                 0                0           
018249          1                 1                1 
827247          0                 1                0             HD32        HD32

I know it is not going to count these, but I wanted to just get a true/false column and only count the true values for my report section of the PBIX.

Is Dax the correct language to perform this, or should I look to use M Code in the Power Query Editor? Any thoughts would be greatly appreciated.

Thanks!

2
powerbidax
What's your expected output? – user8078111

2 Answers

0
votes

Here is measure:

TIE = COUNTROWS (CALCULATETABLE(Sheet2, Sheet2[NORMALPOSITIONA]= 0 || 
Sheet2[NORMALPOSITIONB] = 0 || 
Sheet2[NORMALPOSITIONC]= 0 
&& (Sheet2[FEEDERID] <> Sheet2[FEEDERID2]) 
&& NOT(ISBLANK(Sheet2[FEEDERID2]))))

enter image description here

0
votes

Hi please try the following solution if it helps please mark it as the answer.

  1. Create a calculated column with the following DAX:

    Open FID2 =
IF (
   AND(
       (Table[NPA] = 0 || Table[NPB] = 0 || Table[NPC] = 0 )
        && NOT ( ISBLANK ( Table[FEEDERID2] ) ),
               Table[FEEDERID] <> Table[FEEDERID2]
        ),
   TRUE (),
   FALSE ()
    )

Output