Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
JustinDoh1
Post Prodigy
Post Prodigy

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

I am sharing my Pbix file here.

 

JustinDoh1_0-1629476898236.png

 

 

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.

JustinDoh1_0-1629481773741.png

JustinDoh1_0-1629477098261.png

 

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!

 

 

 

 

2 ACCEPTED SOLUTIONS

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.





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

Alternatively you can try:

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

12 REPLIES 12
JustinDoh1
Post Prodigy
Post Prodigy

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

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

data step.JPG

 

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

return.JPG

 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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

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.





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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

JustinDoh1_0-1629481632256.png

 

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Alternatively you can try:

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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

JustinDoh1_0-1629503880178.png

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_1-1629505083345.png

 

 

@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
Not applicable

But Michael Jackson???

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.