cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper II

## DAX IF OR Measure not working based on criteria

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

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
1 ACCEPTED SOLUTION
Super User

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:

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.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

6 REPLIES 6
Super User

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:

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.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Helper II

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

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

)
Super User

@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.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Helper II

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
Super User

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:

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.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Helper II

Card visual