cancel
Showing results for
Did you mean:
Helper II

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

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

As always any help with this is much appreciated 🙂

1 ACCEPTED SOLUTION
Super User

Hi,

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

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
22 REPLIES 22
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
Helper II

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

Super User

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
Helper II

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.

Super User

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper II

How do I share my PBI file?

Super User

Google Drive or any such similar service.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper II
Super User

Hi,

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

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper II

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.

Super User

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper II

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.

Super User

Hi,

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
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

Helper II

Unfortunately that way it only gives back the first result.

Resolver I

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

Helper II

When I make the measure as a column It gives me the same result for all rows,

I am trying to group it by LabID

Solution Sage

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

Helper II

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

Helper II

Here is the result

Announcements

#### CollabDays Lisbon - 26 November 2022

Sessions include practical, hands-on experience that will help you take the next step in your career and know-how.

#### Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

#### European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

#### Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors
Top Kudoed Authors