Need help with Cascading Filters in Paginated reports using Tabular Model

[Anonymous]

Hi,

I wanted to apply the 3 cascading filters in my report, however i see the duplicate values in the Department filter. How to get the DISTINCT values in the 3rd filter.

EVALUATE SUMMARIZECOLUMNS('Company'[Company], 'Region'[Region], 'Department'[Department], "Actual", [Actual])

I have gone through this blog https://docs.microsoft.com/en-us/power-bi/guidance/paginated-report-cascading-parameter and understood that i need to use distinct,I tried to apply in the above query and received error.

[technolog · Accepted solution]

For your DAX query, if you're seeing duplicate values for 'Department', it's likely because there are multiple rows with the same department but different 'Company' or 'Region' values. To get distinct values for 'Department', you can modify your DAX query using the DISTINCT function.

Here's a way to do it:

EVALUATE
SUMMARIZECOLUMNS(
'Company'[Company],
'Region'[Region],
DISTINCT('Department'[Department]),
"Actual", [Actual]
)
However, if you're using cascading filters, you might want to ensure that the 'Department' filter is only showing values relevant to the selected 'Company' and 'Region'. In that case, you'd need to modify the query to consider the selected values of the first two filters. But for now, the above modification should give you distinct 'Department' values.