topic Re: REMOVE FILTER (Not returning all categories but rather less categories at next level) in DAX Commands and Tips

REMOVE FILTER (Not returning all categories but rather less categories at next level)

n_c — Wed, 15 Jan 2025 12:42:06 GMT

Hi,

I have a tricky situtation wherien I need to report both zip and state related information with data in a narrow format in one table. If i am using remove filter, I am getting returned the numbers corresponding to only 2 categories male and female (while for some reason its hiding the unknown counts).

Test Data - test_data.xlsx

Pbix File - test_exclude_filter_dax.pbix

I am relatively new to the powerbi dax, request the experts to help help me on this.

Regards,

Re: REMOVE FILTER (Not returning all categories but rather less categories at next level)

bhanu_gautam — Wed, 15 Jan 2025 12:57:55 GMT

n_c , Try using this

Measure1 = CALCULATE(

SUM(Sheet1[demo_count]),

ALL(Sheet1[subcat]),

Sheet1[subcat] = "state"

)

Attaching PBIX with new measure check measure1

Re: REMOVE FILTER (Not returning all categories but rather less categories at next level)

n_c — Wed, 15 Jan 2025 13:40:42 GMT

Hi bhanu_gautam ,

Thanks for your prompt reply, I think I was unable to provide clear picture earlier

If one selects the zip code all the cateogries corresponding to state code should show up here (here for example purposes we can assume that the zip code 956 (2 genders) is mapped to CA (3 genders)

I want all information under state level

Re: REMOVE FILTER (Not returning all categories but rather less categories at next level)

n_c — Wed, 15 Jan 2025 14:01:28 GMT

Hi bhanu_gautam ,

Thanks for your prompt reply. I think i was unable to explain the scenario better.

For each zip code there is a tied state code, so for the sake of argument if we say 956 zip code is tied to CA state code,

I will like to see all the 3 categories i.e. male , female, and unknown in the state related information. which is not happening behind the scenes somehow only the categories relevant to zip code level are showing up. but if i dont put the categories on the label field then it show the entire patient count in the cards.

Thanks in advance

Re: REMOVE FILTER (Not returning all categories but rather less categories at next level)

bhanu_gautam — Wed, 15 Jan 2025 14:25:40 GMT

n_c , You are using slicer on subcat , if you want for state use slicer on Subcat

currently output is correct as per your data

Re: REMOVE FILTER (Not returning all categories but rather less categories at next level)

n_c — Wed, 15 Jan 2025 15:44:59 GMT

Hi bhanu_gautam , it was just a quick diy to explain the situation in my original dashboard it is restricted to show only zip3 with input text slicer. Hence, this question. So state should be tied to zip to give upper level hierarchy infomration - zip3 (most granular) -> state > division -> region.