topic Re: My calculation does not work using variable (row context issue or something else?) in DAX Commands and Tips

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

JustinDoh1 — Fri, 20 Aug 2021 17:50:46 GMT

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!

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

Anonymous — Fri, 20 Aug 2021 07:43:11 GMT

But Michael Jackson???

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

JustinDoh1 — Fri, 20 Aug 2021 16:34:31 GMT

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. 🙂 )

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

PaulDBrown — Fri, 20 Aug 2021 17:17:39 GMT

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

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

JustinDoh1 — Fri, 20 Aug 2021 17:52:11 GMT

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

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

PaulDBrown — Fri, 20 Aug 2021 17:38:25 GMT

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.

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

JustinDoh1 — Fri, 20 Aug 2021 17:47:34 GMT

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?

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

PaulDBrown — Fri, 20 Aug 2021 18:05:55 GMT

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

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

PaulDBrown — Fri, 20 Aug 2021 18:08:40 GMT

Alternatively you can try:

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

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

JustinDoh1 — Fri, 20 Aug 2021 18:29:03 GMT

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.

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

PaulDBrown — Fri, 20 Aug 2021 19:08:40 GMT

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

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

JustinDoh1 — Sat, 21 Aug 2021 00:20:03 GMT

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)?

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

JustinDoh1 — Sun, 22 Aug 2021 15:47:30 GMT

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