How to display all the values which is greater than 100 it should ignore further condition?

[Sampathkumar_v]

Hi Folks,

How to display all the values which is greater then 100 using below logic as if now this logic is filtering values only >100 and <200 it's not considering value greater then 200 because of futher condition >200 and >300.

Sales Bucket =
VAR SalesValue = Sales[SalesAmount]
RETURN
SWITCH(
TRUE(),
SalesValue > 300, "> 300",
SalesValue > 200, "201-300",
SalesValue > 100, "101-200",
"<= 100"
)

My requirement is while clicking >100 - to display all the values greater than 100 till max value and
clicking >200 - to display all the values greater than 200 till max value & while clicking >300 - to display all the values greater than 300 till max value.

[Bibiano_Geraldo]

Hi @Sampathkumar_v ,

Please try the bellow DAX:

Sales Bucket =
VAR SalesValue = Sales[SalesAmount]
RETURN
SWITCH(
    TRUE(),
    SalesValue > 300, "> 300",
    SalesValue > 200, "> 200",
    SalesValue > 100, "> 100",
    "<= 100"
)

[DataNinja777]

Hi @Sampathkumar_v ,

To meet your requirements where selecting ">100" should display all values greater than 100, selecting ">200" should display all values greater than 200, and selecting ">300" should display all values greater than 300, you need to adjust your DAX formula to avoid mutually exclusive conditions. Here's how you can modify the formula:

Sales Bucket =
VAR SalesValue = Sales[SalesAmount]
RETURN
SWITCH(
    TRUE(),
    SalesValue > 300, "> 300",
    SalesValue > 200, "> 200",
    SalesValue > 100, "> 100",
    "<= 100"
)

The updated logic progressively checks for values greater than the specified threshold without segmenting them into specific ranges. For example, if the SalesValue is greater than 200, it will fall into the "> 200" bucket, regardless of whether it also satisfies the condition of being greater than 300. This approach ensures a smooth and inclusive classification of values.

When using this logic in a slicer or filter, selecting "> 100" will include all values greater than 100, encompassing those greater than 200 and 300. Similarly, selecting "> 200" will display all values greater than 200, including those greater than 300. Finally, selecting "> 300" will show only values that are greater than 300.

To implement this in visuals, the Sales Bucket column can be used as a slicer or filter, allowing users to choose the required bucket (e.g., "> 100," "> 200," or "> 300"). Based on the selection, the corresponding table or visual will dynamically display all values that meet the specified threshold and include higher values up to the maximum in the dataset. This method ensures the desired flexibility and clarity in filtering and viewing data.

Best regards,

[Sampathkumar_v]

Hi, using below logic it's filter only values greater than 100 and less then 200 because one more condition to  filter the values greater than 200 less then 300. my requirement is if i select from option >100 it should show all the values >100  (want to filter the all the values)

Sales Bucket =
VAR SalesValue = Sales[SalesAmount]
RETURN
SWITCH(
    TRUE(),
    SalesValue > 300, "> 300",
    SalesValue > 200, "> 200",
    SalesValue > 100, "> 100",
    "<= 100"
)

[elitesmitpatel]

just share a dummy data pbix file with similar condtion and expected output , it will help a lot