Forum Discussion
Helper IGet Visualisation to Ignore a Filter
I have a Power BI Report which has a number of Filters, including one for 'AppCycleYear'. On one page. I am trying to create a Table visualisation which shows the CountOfApplications by AppCycleYear for all years, by ignoring the year filter.
This the DAX measure I have written for this, but the problem is that it returns the total CountOfApplications under a single year (i.e. 2023 since that is the year the report level filter is currently selected)
| Count_Applications | AppCycleYear |
| 4,500 | 2023 |
| Count_Applications | AppCycleYear |
| 1,250 | 2023 |
| 1,100 | 2022 |
| 2,150 | 2021 |
How do I write the DAX function so that I get all Data by year, while the report level filter is selecting a single year? Do I need to write a measure for AppCycleYear too?
I guess my question is, is it possible to write DAX that ignores report level filters?
Thanks in advance
I managed to solve this myself by creating a second Calendar table and setting up an inactive relationship between my main table and and the second calendar table.
I then created a measure which relates my applications table to the new calendar table using USERELATIONSHIP and which ignores the original Calendar table using ALL as follows
Count_Applications =CALCULATE(DISTINCTCOUNT(applications[application_id])],USERELATIONSHIP(applications[CompletedDate],'Calendar2'[Date]),ALL('Calendar'[AppCycleYear]))Bit clunky, but it works
2 Replies
SteveODeaI managed to solve this myself by creating a second Calendar table and setting up an inactive relationship between my main table and and the second calendar table.
I then created a measure which relates my applications table to the new calendar table using USERELATIONSHIP and which ignores the original Calendar table using ALL as follows
Count_Applications =CALCULATE(DISTINCTCOUNT(applications[application_id])],USERELATIONSHIP(applications[CompletedDate],'Calendar2'[Date]),ALL('Calendar'[AppCycleYear]))Bit clunky, but it works
