Why IF dax not working for outside filters

[Anonymous]

Hi Guys,

Hope all are doing good,

   I'm also facing the same issue from last few weeks onwards,But this is the basic thing but i'm unable to findout the way for below issue.

Percentage DAX :-

COUNT('Table'[Name])/CALCULATE(COUNT('Table'[Name]),ALLSELECTED('Name'))

Name	Percentages
A	24.36%
B	20.51%
C	15.30%
D	8.97%
E	6.41%
F	6.41%
G	6.41%
H	5.13%
I	3.85%
J	2.56%

I want to display the categories based on percentages cutoff's

IF([Percentage]>= 10, "High","Low")

By default it was showing correct result only.But when  i was tried to apply some date range in filters  by using [Date] column .That time If function is not working.please check in below image.

A	Low	22.58%
B	High	19.35%
C	Low	16.13%
D	Low	16.13%
E	Low	12.90%
F	Low	9.68%
G	High	3.23%

If i write the same IF DAX by using CALCULATED MEASURE.It was showing correct result in all scenarios but i want to display as Pie chart. measures can't take in  Legend  right.

Appriciate for any help.

Regards,

Dinesh.

[v-lili6-msft]

hi  @Anonymous 

the measure should work well, There should be something wrong in other, could you please share your sample pbix file for us have a test? that will be a great help.

Regards,

Lin

[Anonymous]

HI @v-lili6-msft ,@Fowmy @amitchandak 

Please follow the below link for the pbix file.

https://drive.google.com/file/d/1bC9wV2YsR0W6q1_Y9hxw9v3b6VtpCMTQ/view?usp=sharing

In that file i have created calculated column and calculated Measure.For my scenario measure was working fine But i  want to display those categories in column then only i will use it as pie chart legends right.

Please check the file you guys will understand my issue.

Thanks for responding,

Dinesh.

[harshnathani]

Hi @Anonymous ,

You model has many to many relationship.

Delete that and make it one to many relationship. It will work.

Regards,

Harsh Nathani

[Anonymous]

HI @harshnathani ,

I dont have any relationship with another table.All data comes from single table only

Regards,

Dinesh.

[v-lili6-msft · Accepted solution]

hi @Anonymous 

First, you should know that:

1. Calculation column/table not support dynamic changed based on filter or slicer.
2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary result in a visual by its row context.

https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

That means you could not put it into pie chart legend directly.

Second, for your case, you could refer to this same post:

https://community.powerbi.com/t5/Desktop/Use-measure-as-chart-legend/m-p/1237315#M547738

Create a measure like this:

Measure 3 = var _table=FILTER(CROSSJOIN(ADDCOLUMNS(SUMMARIZE('Table','Table'[Name ]),"_Category",[Categories Measure]),'Dim Category'),[_Category]=[Category]) return
COUNTROWS(_table)

and here is sample pbix file, please try it.

Regards,

Lin

[Anonymous]

HI @v-lili6-msft ,

Really thanks alot for your help.

Is there anyway to display the [Name] column values with percentage for when we get the High value in category?

For example :

If percentage of [Name] column values is morethan 30% that time

Instead on showing high,we have to  display [Name] coulmn values like(A,D,C--------etc) otherwise it should show as  "Low"

Thanks,

Dinesh 

[amitchandak]

@Anonymous , formula seems correct try like

maxx(summarize(Table,Table[Name],"_1",COUNT('Table'[Name])/CALCULATE(COUNT('Table'[Name]),ALLSELECTED('Name'))),IF([_1]>= 10, "High","Low"))

[Fowmy]

@Anonymous 

Is it possible to share your PBIX file and tell us where exactly you are not getting the result as you mentioned?  

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn