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.
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 @Dsharma43 ,
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
Check out new user group experience and if you are a leader please create your group
Click here to read more about the April 2021 Updates!
May the fourth be with you, join us online!
100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.