DAX if/then for percentage of applicable length of list

[spayne16]

I have a bunch of states/regions in a report that we like to include in a header so that it changes if someone filters on a region. I've written this DAX: RegionOptions = IF(len([regionlist]) =132, "All", if(len([regionlist]) >15, "Multiple Regions Selected", [regionlist])) and this works great for the pages that include all data. My issue is that subsequent pages are only looking at specific categories, so the length of the region list changes (as some regions are specific to a category) and suddenly it's impossible to get "All". I'd like to somehow change it to a percentage so it can apply to all pages, like RegionOptions = IF(len([regionlist]) =100%, "All", if(len([regionlist]) >40%, "Multiple Regions Selected", [regionlist])) The [regionlist] is simply a list with comma separations (i.e. NY, WI, NorthWest).

Does that make sense? Is this possible? I'm a brand new user.

[Mrxiang]

2023/5/25 11:50:28 (131257)

Yes, I understand your question. Unfortunately, it is not possible to use a percentage in the IF statement in DAX. However, you could try using a measure to calculate the percentage of regions selected and then use that measure in your IF statement. For example, you could create a measure called "RegionPercentage" that calculates the percentage of regions selected like this:

RegionPercentage = DIVIDE(COUNTROWS(VALUES(Table[Region])), COUNTROWS(Table))

Then, you could use this measure in your IF statement like this:

RegionOptions = IF(RegionPercentage = 1, "All", IF(RegionPercentage > 0.4, "Multiple Regions Selected", [regionlist]))

This should give you the desired result. Let me know if you have any further questions.

[spayne16]

Thanks - I think that's on the right path but the RegionPercentage doesn't end up giving the correct percentage.  When I'm showing 100% of the data the first part gives 16 unique regions in the data, but the 2nd part just counts the total rows, which is nearly 8,000, so dividing it gives just .002, when I need it to give me 100%.  The first part is always correct, but I somehow need the 2nd part to give me the total unique region count with all filters except for the region (as other pages will have filters on category and such), and honestly I don't think that's possible - at least not without unique measures for every single page (not feasible).  Disappointing since Tableau does something similar automatically without any measures or calculations.