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

[JustinDoh1]

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 "vNotEligibleStep1 = 1"). I am trying to express "if vNotEligibleStep1 case exisits". 

From the Pbix file that I am sharing, the issue is whenever we have an output (this case: Tina Turner = 1 & Michael Jaskson = 1) with one of three (Doe. D or Doe, W - where we have 'Not Eligible" as "Content" column), it gives a row context error (total does not sum up). I am not sure how to fix it or where the error is.

It is a mesasure called "Step1_0819" in the file.

Am I missing something on the calculation for VAR or how do I go about fixing where it is "vNotEligibleStep1 = 1" ?
Do I have to use Summarize function or how do I go about making the row context more obvious?

Thanks you so much for help!

[PaulDBrown · Accepted solution]

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]

@Anonymous Thank you for response. I would like to follow up if you could help me with any suggestions regards to how to fix the row context issue I am having. (BTW, I added "Tina Turner" in the Pbix file. Sorry I like oldies. 🙂  )

[PaulDBrown]

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]

@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 · Accepted solution]

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]

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

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 · Accepted solution]

Alternatively you can try:

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

[JustinDoh1]

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

[PaulDBrown]

Can you post the updated PBIX file? Maybe we can take it step by step. 

[JustinDoh1]

@PaulDBrown here is a link to the updated Pbix file. I guess I am leaning toward using SUMX (on this particular small data). 

But, when I use other larger datasets (for other column, but with same approach (using Countrows --> SUMX), instead of showing 1, it shows as 2 occurrences. I guess there are duplicate occurrences with same condition. What should I do?  How can I make the dinstinct (SUMX)?

[JustinDoh1]

@PaulDBrown I have created a new question regards to SUMX. I am really struggling to find the solution for getting Total on the bottom of Matrix because if use SUMX, it creates multiple output (rather than 1), but when I use SUMX, Total comes out to be "" (when I have to exclude anything).

[Anonymous]

But Michael Jackson???