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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
CrudenBoy
Frequent Visitor

Issue with ALLSELECT

Hi

I am fairly new to DAX and am having a problem doing an advance calculation. Without going into the full-blown explanation of cohort Surval Rate calculation, I have tried to focus on a small using which I think stems from an ALLSELECT statement I use.

 

If someone can anyone please tell me what I am going wrong I would be extremely grateful!

 

The table below explains my issue. In summary, the measure GC2 works but I have a problem with the measure GC3. I see this is because the measure X3 returns ZEROs in each row and not 0.816 in each row. However, the calculation works in measure X2 

 

The formulas are:

GC2=X2* Y2

GC3=X3* Y3

Where X2 and X3 are generated by the following formulas

X2 = CALCULATE([GC1],
FILTER(Allselected(SurvivalRate),
SurvivalRate[GradeCode]=1))

 

and 

X3 = CALCULATE([GC2],
FILTER(Allselected(SurvivalRate),
SurvivalRate[GradeCode]=2))

You can open this link to my example file which I have published on my Onedrive account, but this table attempts to explain the issue.

 

Table.png

2 ACCEPTED SOLUTIONS
AllisonKennedy
Super User
Super User

@CrudenBoy  What is it you are ultimately trying to acheive? Do GC0, GC1, etc actually need to be in separate columns? I'm wondering if you could use the EARLIER function to create a series of calculated columns in a GradeCode table that has each grade code listed once? 

https://blog.enterprisedna.co/how-to-use-the-earlier-function-in-power-bi-a-dax-tutorial/

Iterators might also help to lock in the row and table context that each measure needs to calculate within, rather than how they are now which is floating across the data model and recalculating depending on how you setup your table.

https://www.microsoftpressstore.com/articles/article.aspx?p=2449191&seqNum=3

EARLIER and Iterators might help you get what you need. 

 

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

Thank you very much for your feedback @Anonymous @AllisonKennedy  and @vivran22 and let me apologise for my limited description of my problem. Alison's post was very useful as it made me read up about the filter context and row context and I have managed to make progress.

 

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

Without explaining the logic behind your measures... you can easily forget about anyone giving you an answer to your problem.

Best
D

Thank you very much for your feedback @Anonymous @AllisonKennedy  and @vivran22 and let me apologise for my limited description of my problem. Alison's post was very useful as it made me read up about the filter context and row context and I have managed to make progress.

 

AllisonKennedy
Super User
Super User

@CrudenBoy  What is it you are ultimately trying to acheive? Do GC0, GC1, etc actually need to be in separate columns? I'm wondering if you could use the EARLIER function to create a series of calculated columns in a GradeCode table that has each grade code listed once? 

https://blog.enterprisedna.co/how-to-use-the-earlier-function-in-power-bi-a-dax-tutorial/

Iterators might also help to lock in the row and table context that each measure needs to calculate within, rather than how they are now which is floating across the data model and recalculating depending on how you setup your table.

https://www.microsoftpressstore.com/articles/article.aspx?p=2449191&seqNum=3

EARLIER and Iterators might help you get what you need. 

 

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

vivran22
Community Champion
Community Champion

Hello @CrudenBoy ,

 

Can you explain the logic behind the calculation for X2, X3, Y2 & Y3 along with example?

 

Cheers!
Vivek

https://www.vivran.in/
Connect on LinkedIn

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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.