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

I see you've changed the measure, since this step was always going to return 1

 

Now the rogue variable returns a date, (specifically the MAX(Data[consentdate])based on the filters) but the first clause in the switch function checks to see if the variable returns 1 (a whole number; not a date). So I would assume the result is always false, since a date is never "1"...

 

 

JustinDoh1's avatar
JustinDoh1
Post Prodigy
5 years ago

PaulDBrown Thank you for your help. Yea. I was modifying the original file as I put [ConsentDate] instead of [Consent].  I was/am trying to create a logic "exists".

 

I guess " = 1" was silly. Here is what I am trying to say:  If on the Row context level, if this particular row meets this condition (Step =1 & Consent = "Not Eligible"), result should be "".

  • 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]))))