cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
New Member

Filter tables by DAX formula results

Hi 

 

I have a formula that I would like to use to create a visual slicer, the formula returns estimated value brackets and I want to create a visual slicer for the end-user to be able to filter the entire report based on those brackets.

 

The formula looks like this:

 

EstimateValueRange =
IF(
SUM(opportunitystats[calcd_Current_value_LCY])>-1 && SUM(opportunitystats[calcd_Current_value_LCY])<=200000,"<=200k",
IF(
SUM(opportunitystats[calcd_Current_value_LCY])>200001 && SUM(opportunitystats[calcd_Current_value_LCY])<=1000000,"200k - 1M",
IF(
SUM(opportunitystats[calcd_Current_value_LCY])>1000001 && SUM(opportunitystats[calcd_Current_value_LCY])<=2000000,"1M- 2M",
IF(
SUM(opportunitystats[calcd_Current_value_LCY])>2000001 && SUM(opportunitystats[calcd_Current_value_LCY])<=99000000,"<=2M"))))
 
And it returns this
 
image.png
 
How can I use that last column to create a new table or feed a visual slicer?
 
When I try to add a new column to opportunitystats table and use that formula, I only get that first bracket <=200k
image.png
 
Thank you in advance
1 ACCEPTED SOLUTION
Super User II
Super User II

@Godfather3300 

 

Are you creating a measure or column for this?

 

Are there any duplicated values in No column? If yes, you can't create a column in the table.

If there are all unique values, you can use switch value, which is easier to write.

Something like below.

column = SWITCH(TRUE(),[LCY]<500,"1-500",[LCY]<1000,"501-1000"))

 

If there are duplicated values in the No column ,you can transform the table.

1.PNG

Try below to creating a new table

Table 2 = ADDCOLUMNS(SUMMARIZE('Sheet5',Sheet5[NO],"LCY",sum(Sheet5[LCY])),"scope",SWITCH(TRUE(),[LCY]<500,"1-500",[LCY]<1000,"501-1000"))

2.PNG

hope this is helpful.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

5 REPLIES 5
Super User II
Super User II

@Godfather3300 

 

Are you creating a measure or column for this?

 

Are there any duplicated values in No column? If yes, you can't create a column in the table.

If there are all unique values, you can use switch value, which is easier to write.

Something like below.

column = SWITCH(TRUE(),[LCY]<500,"1-500",[LCY]<1000,"501-1000"))

 

If there are duplicated values in the No column ,you can transform the table.

1.PNG

Try below to creating a new table

Table 2 = ADDCOLUMNS(SUMMARIZE('Sheet5',Sheet5[NO],"LCY",sum(Sheet5[LCY])),"scope",SWITCH(TRUE(),[LCY]<500,"1-500",[LCY]<1000,"501-1000"))

2.PNG

hope this is helpful.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Thanks, that worked perfectly, the No column is unique and the Switch formula to add a column did the trick.

 

Thank you so much!!

Super User III
Super User III

Hi @Godfather3300 ,

 

Can you try modifying your DAX as follows:

 

EstimateValueRange =
IF(
(opportunitystats[calcd_Current_value_LCY])>-1 && (opportunitystats[calcd_Current_value_LCY])<=200000,"<=200k",
IF(
(opportunitystats[calcd_Current_value_LCY])>200001 && (opportunitystats[calcd_Current_value_LCY])<=1000000,"200k - 1M",
IF(
(opportunitystats[calcd_Current_value_LCY])>1000001 && (opportunitystats[calcd_Current_value_LCY])<=2000000,"1M- 2M",
IF(
(opportunitystats[calcd_Current_value_LCY])>2000001 && M(opportunitystats[calcd_Current_value_LCY])<=99000000,"<=2M"))))
 
Thanks,
Pragati




If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




Thank you for the quick response

 

I tried that first, This is what I get

 

image.png

 

A single value for column 'calcd_Current_value_LCY' in table 'opportunitystats' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors