Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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 ...
Need Help
  • AllisonKennedy's avatar
    AllisonKennedy
    3 years ago

    Anonymous 

     

    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. 

AllisonKennedy's avatar
AllisonKennedy
Icon for Community Champion rankCommunity Champion
3 years ago

Anonymous 

 

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. 

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
    AllisonKennedy
    Icon for Community Champion rankCommunity Champion
    3 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Card visual

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi 

       

      No luck with the max and here is the sample data

      Sample Data

      CountWorktypeStatusMEUCOMPLIANCEIMPACT
      1RWPPM0
      1RWPPM0
      1RWPPM0
      1RWPPM0
      1REMSTPPM0
      1REMSTPPM0
      1REMSTPPM0
      1REMSTPPM

      0

       

      • AllisonKennedy's avatar
        AllisonKennedy
        Icon for Community Champion rankCommunity Champion
        3 years ago

        Anonymous 

         

        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.