I am trying to return all the rows(category) from the table1 irrespective of filter is applied on the report. If there is no value then show null values instead of rows getting disappeared.
Could you please advise on how can i achieve this.
I have enabled option show items with no data but still rows get disappared as soon as i apply filter.
|Business Disruption and System Failure|
|Third Party Outsourcing|
|metric4||Third Party Outsourcing||9/30/2019|
Screenshot1(without applying filter)
Screenshot2(after applying filter) -> here rows get disappeared as soon as i apply filter on the report. i want report to show all the categories(levels) and show blank or null if filter is not applicable.
I was facting a similar issue where I wanted to show all distinct values from one column as the rows reguardless of filters. Then show a measures which was a sum of another column in the same table. I wanted the filter to apply to the values, which was a measure, but not the rows.
When filters would be applied from a slicer the rows would get filtered even if I selected "Show Items with no Data".
My "workaround" was to create a calculated table that contained all the distinct values from Column1. Then create a relationship between the calculated dax table and the origional table. Then I selected column1 from the calculated table to be the rows. After doing that I selected "Show Items with no data" and showed all the rows regardless of the filters applied.
Thanks for your response.
For an example in below snapshot without applying filter it show all rows. As soon as i apply path to green indicator it filters the rows to 5 or 6(screenshot2) instead i want all the rows if PTG is not applicable then show me blank/null values.
Remove the interaction between slicer and the table.
Join the table 1 and 2 on level and in the slicer use level column fron table1.
Create the below measure and use it instead of category_composite_current_month_1 column
Mearure = VAR _Slicer = SELECTEDVALUE('Table1'[level]) RETURN IF('Table2'[level] = _Slicer, 'table'[category_composite_currentmonth],blank())
Hope it resolves your issue?
Did I answer your question? Mark my post as a solution!
Appreciate your Kudos, Press the thumbs up button!!
Hi @pranit828 ,
i dont want to change the slicer to level , i want to have Path to green indicator as a slicer and then filter the table using that.
Hi @Anonymous ,
Do you have a relationship between table 1 and table2? Delete it and use level column from table1 and add filter to formula table2[level] = MAX(table1[level]). The slicer will not filter the level column from table1 any more?
If you need accurate formula, please show us sample pbix by onedrive for business.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@pranit828 thanks for the response.
what is the measure calculation for your category composite current month? it sounds like you need an all() statement in it. All(tablename), will ignore all filters.
Thanks for the response.
here is my calculation
Take a look at the September 2023 Power BI update to learn more.
Join Microsoft Reactor and learn from developers.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!
Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.