DAX IF OR Measure not working based on criteria

[apple1111]

Hi Experts

I cannot work out whats wrong with the following Measure

Criteria

If Worktype = RM and MEUCOMPLIANCEIMPACT = 1  then VAR _A

if Worktype = REMST and MEUCOMPLIANCEIMPACT = 0 the VAR _B 

Measure

Remedial Tasks =

VAR _A = CALCULATE([Count],          

                FILTER(USVF_WO_22_12_22,USVF_WO_22_12_22[WORKTYPE] = "RW" && USVF_WO_22_12_22[MEUCOMPLIANCEIMPACT] = 1),

                       USVF_WO_22_12_22[STATUS] <> "CAN",

                       ALL(DimDate[Date]))

VAR _B = CALCULATE([Count],          

                FILTER(USVF_WO_22_12_22,USVF_WO_22_12_22[WORKTYPE] = "REMST"),

                       USVF_WO_22_12_22[STATUS] <> "CAN",

                       ALL(DimDate[Date]))

Return

    IF(OR(_A,_B),0)+0      

Sample Data

Count	Worktype	Status	MEUCOMPLIANCEIMPACT
1	RW	PPM	0
1	RW	PPM	0
1	RW	PPM	0
1	RW	PPM	0
1	REMST	PPM	0
1	REMST	PPM	0
1	REMST	PPM	0
1	REMST	PPM	0

[AllisonKennedy · Accepted solution]

@apple1111 

You haven't given the Criteria to the IF statement. 

Since you have more than 1 criteria, it may be easier to use a SWITCH:

keep the top part of your measure, but change the RETURN to:

RETURN

SWITCH( TRUE(),

SELECTEDVALUE(Table[Worktype] )= "RM" && SELECTEDVALUE([MEUCOMPLIANCEIMPACT] )= 1  ,  _A, 

SELECTEDVALUE(Table[Worktype] )= "RMST" && SELECTEDVALUE([MEUCOMPLIANCEIMPACT])  = 0 , _B 

)

Since it's a measure, you'll need to aggregate the Worktype and MEUCOMPLIANCEIMPACT columns in some way. I chose SELECTEDVALUE, but you could use MAX or SUM depending on how you want it to work. 

[apple1111]

Hi Allison - firstly thanks for looking at the question but i am getting a blank as th end result when a value is expected

fUll measure

Remedial Tasks =

VAR _A = CALCULATE([Count],          

                FILTER(USVF_WO_22_12_22,USVF_WO_22_12_22[WORKTYPE] = "RW" && USVF_WO_22_12_22[MEUCOMPLIANCEIMPACT] = 1),

                       USVF_WO_22_12_22[STATUS] <> "CAN",

                       ALL(DimDate[Date]))

VAR _B = CALCULATE([Count],          

                FILTER(USVF_WO_22_12_22,USVF_WO_22_12_22[WORKTYPE] = "REMST"),

                       USVF_WO_22_12_22[STATUS] <> "CAN",

                       ALL(DimDate[Date]))

RETURN

SWITCH( TRUE(),

SELECTEDVALUE(USVF_WO_22_12_22[WORKTYPE] )= "RM" && SELECTEDVALUE(USVF_WO_22_12_22[MEUCOMPLIANCEIMPACT] )= 1  ,  _A,

SELECTEDVALUE(USVF_WO_22_12_22[WORKTYPE] )= "RMST" && SELECTEDVALUE(USVF_WO_22_12_22[MEUCOMPLIANCEIMPACT])  = 0 , _B

)    

[AllisonKennedy]

@apple1111  What visual context are you trying to use this in? Try changing the SELECTEDVALUE to max and see if that gives you a result, then you need to understand what that is doing as it may not be what you want (which is why I chose SELECTEDVALUE as I find it better to give blank than an incorrect / misleading result). 

You need to make sure that you're using WorkTYPE and MEUCOMPLINCEIMPACT columns in the visual where you're using this measure. 

If that still doesn't help, please provide more sample data and include the Date column and any relationships. 

[apple1111]

Hi 

No luck with the max and here is the sample data

Sample Data

Count	Worktype	Status	MEUCOMPLIANCEIMPACT
1	RW	PPM	0
1	RW	PPM	0
1	RW	PPM	0
1	RW	PPM	0
1	REMST	PPM	0
1	REMST	PPM	0
1	REMST	PPM	0
1	REMST	PPM	0

[AllisonKennedy]

@apple1111 

That doesn't look like the raw data, as there are no dates and the 'count' should be a measure, but if you want to use it in a card you need to provide the context within the measure. 

Try this: 

Remedial Tasks =

VAR _A = CALCULATE(COUNTROWS(apple1111),          

                FILTER(apple1111,apple1111[WORKTYPE] = "RW" && apple1111[MEUCOMPLIANCEIMPACT] = 1),

                       apple1111[STATUS] <> "CAN",

                       ALL(DimDates[Date]))

VAR _B = CALCULATE(COUNTROWS(apple1111),          

                FILTER(apple1111,apple1111[WORKTYPE] = "REMST"),

                       apple1111[STATUS] <> "CAN",

                       ALL(DimDates[Date]))

RETURN

SUMX(apple1111,

SWITCH( TRUE(),

apple1111[WORKTYPE] = "RM" && apple1111[MEUCOMPLIANCEIMPACT] = 1  ,  _A,

apple1111[WORKTYPE] = "REMST" && apple1111[MEUCOMPLIANCEIMPACT] = 0 , _B

)  

)

But can you explain in English what you you want as I think we can optimize this for you better.

[apple1111]

Card visual