Cascading Parameters using DAX query is not filtering the child paramteres

[GreeshmaN]

I have a model with a fact and dimension tables.

dim1 table, I have master_product_id
dim2 table, I have product_id.
I have created cascading parameters using DAX instead of query designer. When a user filter master_product_id the eventually the product_id should display data for that specific master_product_id.

A particular master_product_id can have multiple product_id's. I had the below logic which worked fine until july, all of a sudden it's not filtering the product_id values.

The below query worked fine but it's not filtering the data any more.
Param_Prductid:
EVALUATE
CALCULATETABLE (
SUMMARIZECOLUMNS ( 'dimen_product'[prd_id] ),
'dim_prd_master'[master_prd_id] = @Masterprdtid
)
ORDER BY [prd_id] ASC

I checked in DAX studio it shows all the prd_id's.

I am using PBI Dataset as my source in paginated report.

Any thoughts on what went wrong?

[GreeshmaN]

Looks like Calculatetable() is not working properly where it's not filtering the data for the parameter that we applied.
I tried multiple ways and none of the above solutions are working. 

I am not sure if there is any known issue with calculatetable() or summarizecolumns(). But strange thing is the logic worked before with calculatetable. 

I tried an other logic to filter the product_id for the master_product_id.
EVALUATE
CALCULATETABLE(
VALUES(dim_product[prd_id]),
FILTER(
dim_master_product,
dim_master_product[master_prd_id]= @Masterprdid
)
)
ORDER BY [prd_id] ASC

[divyed]

Hello @GreeshmaN ,

You can modify your DAX like below :

EVALUATE
CALCULATETABLE (
SUMMARIZECOLUMNS (
'dimen_product'[prd_id],
'dim_prd_master'[master_prd_id]
),
'dim_prd_master'[master_prd_id] = @Masterprdtid
)
ORDER BY [prd_id] ASC

I am assuming you have correct relationship in place and passing parameter correctly.

I hope this helps.

Cheers.

[Kedar_Pande]

@GreeshmaN 

Update your DAX query to ensure proper filtering:

Param_Productid =
EVALUATE
CALCULATETABLE(
SUMMARIZECOLUMNS('dim_prd_product'[prd_id]),
'dim_prd_master'[master_prd_id] = @Masterprdtid,
ALL('dim_prd_product') -- Ensures no other filters conflict
)
ORDER BY [prd_id] ASC

Ensure @Masterprdtid matches the data type of master_prd_id.

Ensure cascading parameters are set correctly in your paginated report.

Ensure the filter context in the Power BI dataset and paginated report is properly applying the @Masterprdtid.

[GreeshmaN]

Hi @Kedar_Pande 
I tried the query that you shared in DAX studio, it's not filtering the values for that specific master_prd_id

[v-linhuizh-msft]

Hi @GreeshmaN ,

You can try using the following query:

EVALUATE
SUMMARIZE(FILTER('dimen_product','dimen_product'[master_product_id] = @Masterprdtid),dimen_product[product_id],dimen_product[master_product_id])
ORDER BY [master_product_id] ASC

Results:

Best Regards,
Zhu

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.