topic Re: Having issue with one of the DAX calculation that I am writing, Dax is somehow filtering categories in DAX Commands and Tips

Having issue with one of the DAX calculation that I am writing, Dax is somehow filtering categories

n_c — Sat, 07 Dec 2024 07:26:13 GMT

Hi @members PowerBI Everyone,

I am new to Powerbi and having some issue that I am trying to fix. Any help will be appreciated

I have data columns such as strata_val, products,units, statelookup. wherein both state and zip level information are existing under the strata_val.

I am writing a dax to removefilter on slicer selection (populating using strata_val(zip)) and using the statelook to create a stratified state level information.

Challenge is that, its filtering the categories on basis on whatever is exisiting only in the zip level. So, if state level has 4 categories, and zip has only 3 categories information, then using dax the chart is returning only 3 category specific information, while i want it to return all the 4 categories

Measure = CALCULATE(sum('Table'[Units]),REMOVEFILTERS('Table'[strata_val]),'Table'[strata_val] = SELECTEDVALUE('Table'[LookupState]))

Snapshot

Sample data

country	strata_val	Product	Units	LookupState
US	WA	A	1
US	WA	B	2
US	WA	C	3
US	WA	D	4
US	980	A	1	WA
US	980	B	2	WA
US	980	C	3	WA
US	980	D	3	WA
US	981	D	1	WA

Re: Having issue with one of the DAX calculation that I am writing, Dax is somehow filtering categories

Poojara_D12 — Sat, 07 Dec 2024 09:41:04 GMT

Hi n_c

Please try this DAX:

Measure = CALCULATE( SUM('Table'[Units]), REMOVEFILTERS('Table'[strata_val]), 'Table'[LookupState] = SELECTEDVALUE('Table'[LookupState]) ) + 0

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Please Subscribe my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS

Re: Having issue with one of the DAX calculation that I am writing, Dax is somehow filtering categories

n_c — Sat, 07 Dec 2024 11:37:29 GMT

Hi Poojara_D12 ,

I tried the suggested resolution, but still it doesnot work.

Re: Having issue with one of the DAX calculation that I am writing, Dax is somehow filtering categories

powerbiexpert22 — Sun, 08 Dec 2024 01:04:37 GMT

Hi n_c ,

this issue is happening because there are no entries in Lookupstate column as highlighted below, please share the sample pbix file if possible ( you can share fie via google drive or dropbix)

Re: Having issue with one of the DAX calculation that I am writing, Dax is somehow filtering categories

n_c — Sun, 08 Dec 2024 05:04:08 GMT

Hi powerbiexpert22 ,

Please find the link to pbix file - https://drive.google.com/file/d/1Et7s9Qw9t4Ge3moVGyCS-8cmcewuXqDu/view?usp=drive_link.

Please feel free to let me know in case of any access issues.

Thanks

Re: Having issue with one of the DAX calculation that I am writing, Dax is somehow filtering categories

powerbiexpert22 — Sun, 08 Dec 2024 08:28:05 GMT

Hi n_c ,

please follow below steps , please see below file for your reference

https://drive.google.com/file/d/12krVWc2G0SinknBrGKyCLhMUgGUuHT94/view?usp=drive_link

1. disable the interacion between chart and slicer uisng Format->Edit interaction

2. use this measure in chart

var Measure_test = CALCULATE(SUM('Table'[Units]),FILTER('Table','Table'[LookupState]=

CALCULATE(MAX('Table'[LookupState]),'Table'[strata_val]=SELECTEDVALUE('Table'[strata_val]))

))

Re: Having issue with one of the DAX calculation that I am writing, Dax is somehow filtering categories

n_c — Sun, 08 Dec 2024 17:24:45 GMT

Hi powerbiexpert22 ,

I tried the solution, it seems to be working for one state code, but if i add up mutliple states then it seems to be breaking and getting only 1 set of static chart.

Please find the updated pbix file attached herein for your reference.

https://drive.google.com/file/d/1LpRjZGW-a2glLvLfQKZ_mFrIKHQvbOxz/view?usp=sharing

Do, I need to make any changes to make it work in case of multiple states.

Re: Having issue with one of the DAX calculation that I am writing, Dax is somehow filtering categories

powerbiexpert22 — Mon, 09 Dec 2024 00:12:23 GMT

Hi n_c ,

you will have to use "var Measure_test_newone" instead of uisng Max(units) in the chart, please see below updated version of pbix

https://drive.google.com/file/d/1OghvvH-Z84Jr2sHmGA_eaZPnfFlfJBha/view?usp=drive_link

Re: Having issue with one of the DAX calculation that I am writing, Dax is somehow filtering categories

n_c — Mon, 09 Dec 2024 04:17:30 GMT

hI powerbiexpert22 ,

I tried the solution, it doesnot seems to be working fine. My expectation is when i select a slicer corresponding to CA it should show bar for CA related data below , if WA then WA specific information. there are 2 zip codes for both in the file with variation but its only picking the products that are in the zip level(i think)

Regards,

Re: Having issue with one of the DAX calculation that I am writing, Dax is somehow filtering categories

powerbiexpert22 — Mon, 09 Dec 2024 09:01:36 GMT

Hi n_c ,

Please follow below steps, please see below pbix as reference

https://drive.google.com/file/d/1L9nKV8y02zltW5SJ6oHMn2pfEbQ9dWbP/view?usp=drive_link

1. create diconnected table

dimID = VALUES('Table (2)'[strata_val])

2. use the column of disconnected table as slicer (no need to enable edit interaction)

3. use below measure

var Measure_test_newone =

CALCULATE (

SUM ( 'Table (2)'[Units] ),FILTER('Table (2)','Table (2)'[LookupState]=

LOOKUPVALUE('Table (2)'[LookupState],'Table (2)'[strata_val],SELECTEDVALUE(dimID[strata_val]))

))