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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

IF statement only returning 2 results despite their being 6 conditions.

I am currently trying to make a nested IF statement which returns results based on what range the value ProportionOfSamplesOver2000 sits in. 

 

The problem is that my nested IF statment only ever returns two of the 6 results, despite there being values within all ranges of my condtions.

 

The DAX code for the IF statement is:

Percentage Groups = if(
'PhenRData'[ProportionOfSamplesover2000] <=0.1, "<10%",


if('PhenRData'[ProportionOfSamplesover2000] >0.1 || 'PhenRData'[ProportionOfSamplesover2000]<= 0.2, "10-20%",


if('PhenRData'[ProportionOfSamplesover2000] >0.2 ||'PhenRData'[ProportionOfSamplesover2000]<= 0.3, "20-30%",


if('PhenRData'[ProportionOfSamplesover2000] >0.3 || 'PhenRData'[ProportionOfSamplesover2000]<= 0.4, "30-40%",


if('PhenRData'[ProportionOfSamplesover2000] >0.4 || 'PhenRData'[ProportionOfSamplesover2000]<= 0.5,"40-50%",">50%")))))

 

This what some of the values for ProportionOfSamplesover2000 are to show that they are more than just under 0.1 and over 0.5.

 

Screenshot 2022-09-09 140501.png

 

 

Here is the range of results I get, I am also wanting 10-20, 20-30,30-40,40-50 

 

Screenshot 2022-09-09 150643.png

 

As always any help with this is much appreciated 🙂

1 ACCEPTED SOLUTION

Hi,

You may download my PBI file from here.  The column shows the numebr of LabID's.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

22 REPLIES 22
Ashish_Mathur
Super User
Super User

Hi,

Your formula can be simplified to

 

Percentage Groups = if('PhenRData'[ProportionOfSamplesover2000] <=0.1, "<10%",if( 'PhenRData'[ProportionOfSamplesover2000]<= 0.2, "10-20%",if('PhenRData'[ProportionOfSamplesover2000]<= 0.3, "20-30%",if('PhenRData'[ProportionOfSamplesover2000]<= 0.4, "30-40%",if( 'PhenRData'[ProportionOfSamplesover2000]<= 0.5,"40-50%",">50%")))))

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Thanks for your reply

 

Unfortunately that doesn't work, it was suggested above and I responded with the result I got.

Write this as a measure (not as a calculated column).  If it still does not help, then share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

That works when I put it into a table visual, but I am looking to put those percentage groups on the X-axis of a column chart, which it wont allow me to do. Do you know why this might be? When adding the percentage groups measure to the column chart It wont move out of tooltips.Screenshot 2022-09-12 144602.png

 

Hi,

Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

How do I share my PBI file?

Google Drive or any such similar service.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hi,

You may download my PBI file from here.  The column shows the numebr of LabID's.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

That has worked to group each pegentage group, thank you very much for your help.

 

If it wouldn't be to much of a hassle, would you be able to explain how the 'measure' works that you added.

 

Thanks again.

You are welcome.  Please read up on dynamic segmentation.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hi Ashish,

 

I have been researching into Dynamic segmentation but it has not helpd me understand the statements you have made here. I'm struggling to understand how the measure that groups the data works. Like what is the "abcd" doing. Any explanation would be much apprecaited.

 

Thanks again.

Hi,

ABCD is just the title of the column.  You can give any other title you want.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
VIJAYKUMART
Resolver I
Resolver I

Try With below DAX:

Percentage Groups = if('PhenRData'[ProportionOfSamplesover2000] <=0.1, "<10%", if('PhenRData'[ProportionOfSamplesover2000]<= 0.2, "10-20%", if('PhenRData'[ProportionOfSamplesover2000]<= 0.3, "20-30%", if('PhenRData'[ProportionOfSamplesover2000]<= 0.4, "30-40%", if('PhenRData'[ProportionOfSamplesover2000]<= 0.5,"40-50%",">50%")))))

 

Hope it helps.

Thanks 

Vijay

Anonymous
Not applicable

Unfortunately that way it only gives back the first result.Screenshot 2022-09-09 164701.png

 

how this column derived  PhenRData'[ProportionOfSamplesover2000]. I think issue with column   PhenRData'[ProportionOfSamplesover2000]. You need derive a column  "ProportionOfSamplesover2000" for % rather than measure


sample data extract

VIJAYKUMART_3-1662706098001.png

 

 

Anonymous
Not applicable

When I make the measure as a column It gives me the same result for all rows,Screenshot 2022-09-12 103441.png

 

I am trying to group it by LabID

 

Shahfaisal
Solution Sage
Solution Sage

The conditions are not mutually exclusive. Do you mean to use AND (&&) instead of OR (||)?

Anonymous
Not applicable

Thanks for your reply.

 

I have tried both, I have it currently set to && and it's still the same.

 

 

Anonymous
Not applicable

Screenshot 2022-09-09 153240.png

 

Here is the result

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.