'Greater than' slicer selection with list selection type

[NJ81858]

Hello,

I currently have a custom column that puts total sales into 1 of 3 groups: "$25k-$49k", "$50k-$100k", and "> $100K" based on the amount of total sales, and a slicer that lists these categories for filtering my sales data:

However, I just got a request to change these from ranges to a "Greater than" value, while keeping the same List selection type in the slicer, so these new values would be "> $25k", which would filter the data to anything greater than $25k, ">$50k", which would filter the data to anything greater than $50k, and "> $100k", which would give anything greater than $100k.

The issue that I am running into, is that this cannot be a calculated column, as there is overlap in the categories, and this cannot be a "greater than" type slicer, as the end user wants to be able to select the value and filter it, rather than dragging a slider. Is this possible? Thank you in advance!

[Ashish_Mathur]

Hi,

I answered a similar question here.  In the Category table, just changed the Upper limit column of each row to a very large number. 

[NJ81858]

@Ashish_Mathur This is what I currently have that puts my values into groups:

Group = IF ( AND( [Sales] >= 25000, [Sales] <= 50000), "$25k-$50k",
IF ( AND( [Sales] > 50000, [Sales] =< 100000), "$50k-$100k",
IF ( [Sales] > 100000, "> $100k", "< $25k")))

My issue is that I would like the values to be a Greater Than, rather than a Between, with the categories being "> $25k",  "> $50k", and "> $100k". In other words, a value of $125k would be in all categories, since it is greater than $25k, $50k, and $100k.

[Ashish_Mathur]

Hi,

You have not followed my advise from the previous post.

[Kishore_KVN]

@NJ81858 If my understanding is correct, you just want to create column saying just >25, >50 and >100 without other information so you can use either 'SWITCH' or 'IF' logic. 

Please find example DAX below:

Slicer Column = If(Sales>=25k&&Sales<50k,">25k",
                If(Sales>=50k&&Sales<100k,">50k",
                If(Sales>=100k,">100k", Blank())))

If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!

[NJ81858]

@Kishore_KVN 

What you suggested is what I currently have, what I need is a way to have each category extend from the bottom mark to the maximum value for sales. In other words, the "> $25k" category needs to include everything from $25k to the maximum value, so there ends up being overlap in the categories. For example, a sales value of $125k would be in all three categories, since it is greater than $25k, $50k, and $100k. Currently, the only way that I know how to achieve this is by having my slicer be a "greater than" type, like this:

The issue is that the end users are requesting it to be the "list" type slicer like in my original post, so I would like it to be in that format.

[Kishore_KVN]

@NJ81858 can you please confirm that in the given example, $125K should be considered in >$25K, >$50K and also in >$100k right?

[NJ81858]

@Kishore_KVN Yes that is correct, $125k would be in every category.

[Kishore_KVN · Accepted solution]

Okay @NJ81858 in that case you have to create a new table and then create slicer:

New Table DAX will be as below:

Slicer Table = 
UNION(
ADDCOLUMNS('Table',
            "Sales_Ref",'Table'[Sales]>25,
            "Slicer",">25"
            ),
ADDCOLUMNS('Table',
            "Sales_Ref",'Table'[Sales]>50,
            "Slicer",">50"
            ),
ADDCOLUMNS('Table',
            "Sales_Ref",'Table'[Sales]>100,
            "Slicer",">100"
            )
)

In this new table you have to create another column as below:

Final Slicer = IF('Slicer Table'[Sales_Ref]=TRUE(),'Slicer Table'[Slicer],BLANK())

Output looks like this:

Now create relationship between these two tables on product and use cross filtering as both direction.

Then output looks like this:

Please note that table can be created in a bit easy way but I am not able to recall it now. 

If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!