Forum Discussion

JustinDoh1's avatar
JustinDoh1
Post Prodigy
5 years ago
Solved

My calculation does not work using variable (row context issue or something else?)

I am sharing my Pbix file here.       I posted a similar question yesterday, but this is little bit different (because I am trying to see whether somehow my logic has an error: ex "vNotElig...
Help with DAX
  • PaulDBrown's avatar
    PaulDBrown
    5 years ago

    How about this for the variable:

     

    Var vNotEligibleStep1 = 
    COUNTROWS(
        FILTER(
            Data,
            Data[Consent] = "Not Eligible"  &&
            Data[Step] = 1
        )
    )

     

     

    This will return 1 for each row which meets the filtered criteria. You can now keep the switch function clause.

  • PaulDBrown's avatar
    PaulDBrown
    5 years ago

    Alternatively you can try:

    new measure = SUMX(Data, [Step 1_0819])

PaulDBrown's avatar
PaulDBrown
Community Champion
5 years ago

How about this for the variable:

 

Var vNotEligibleStep1 = 
    COUNTROWS(
        FILTER(
            Data,
            Data[Consent] = "Not Eligible"  &&
            Data[Step] = 1
        )
    )

 

 

This will return 1 for each row which meets the filtered criteria. You can now keep the switch function clause.

JustinDoh1's avatar
JustinDoh1
Post Prodigy
5 years ago

PaulDBrown Thank you for help. It definitely made the argument works. However, I see the total comes as 4 instead of 2. Is there way to fix this by chance?

 

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    5 years ago

    The problem appears to be the variable Step1.

    try:

    CALCULATE(

    DISTINCTCOUN(Data[ClientID]),

    FILTER(Data,

    Data[Step] = 1 && Data[Consent] <> "Refused" && NOT(ISBLANK(Data[Consent]))))

    • PaulDBrown's avatar
      PaulDBrown
      Community Champion
      5 years ago

      Alternatively you can try:

      new measure = SUMX(Data, [Step 1_0819])

      • JustinDoh1's avatar
        JustinDoh1
        Post Prodigy
        5 years ago

        PaulDBrown Actually, SUMX made more output of rows, so I think it might not be the solution, but I need to review data more. Thank you so much for all your help.