Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

And or Count DAX Measure not giving correct result

Hi Experts

 

i am trying to work out the following based on the table below...

 

If Worktype = RW and MEUCOMPLIANCEIMPACT = 1 and Status <> "CAN", then count  or otherwise Worktype = "REMST" and 

MEUCOMPLIANCEIMPACT. Measure below not getting the correct result. In the sample data below RW is not 1 but zero so we count the occurance on REMST which is 4

 

Measure

 

Remedial Tasks =
                if(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",
                       TREATAS(VALUES(DimDate[Date]),USVF_WO_22_12_22[TARGCOMPDATE])),
                       CALCULATE([Count],          
                FILTER(USVF_WO_22_12_22,USVF_WO_22_12_22[WORKTYPE] = "REMST"),
                       USVF_WO_22_12_22[STATUS] <> "CAN",
                       TREATAS(VALUES(DimDate[Date]),USVF_WO_22_12_22[TARGCOMPDATE])))+0

 

 

Need Help

3 Replies

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

    Anonymous Sorry, having trouble following, can you post sample data as text and expected output?
    Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

    Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

    The most important parts are:
    1. Sample data as text, use the table tool in the editing bar
    2. Expected output from sample data
    3. Explanation in words of how to get from 1. to 2.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi Greg See sample data as text - the expected end result is 4 as we have no MEUCOMPLIANCEIMPACT = 1 where WorkType = RW...but we have REMST we count those values and we have 4 of those as per below

      CountWorktypeStatusMEUCOMPLIANCEIMPACT
      1RWPPM0
      1RWPPM0
      1RWPPM0
      1RWPPM0
      1REMSTPPM0
      1REMSTPPM0
      1REMSTPPM0
      1REMSTPPM0
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Tried the following still not working

      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