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

[n_c]

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

[powerbiexpert22]

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]))

))

[n_c]

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.

[powerbiexpert22]

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

[n_c]

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,

[powerbiexpert22 · Accepted solution]

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]))

    ))

[powerbiexpert22]

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)

[n_c]

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

[n_c]

Hi @Poojara_D12 ,

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

[Poojara_D12]

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