Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

return all rows from table1 irrespective of filter applied

Hi,

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.

 

Table1
Level
Information Security
Business Disruption and System Failure
Third Party Outsourcing
Technology

 

 

Table2      
Metric_nameLevelDerivedvalueDatemetric_identifierPTGPerformance
Metric1Information Security210/30/2020Mandatoryyesyes
Metric1Information Security310/30/2020Mandatoryyesyes
Metric1Information Security49/30/2020Mandatoryyesyes
Metric2Technology110/30/2020Indicativeyes 
metric3Technology29/30/2020   
metric4Third Party Outsourcing 9/30/2019   

 

 

Example :

Screenshot1(without applying filter)

 

Capture1.PNG

 

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.

 

here i want to show all the rows, show rows with null or blank values if filter is not applicablehere i want to show all the rows, show rows with null or blank values if filter is not applicable

 

9 REPLIES 9
davidwc
Frequent Visitor

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.

Anonymous
Not applicable

@pranit828  @vanessafvg 

 

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.

 

Filter1.PNG

 

 

Screenshot2

Filter2.PNG

Hi @Anonymous 

Remove the interaction between slicer and the table. 

pranit828_0-1611691467442.png

 

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())

 

 

 





PBI_SuperUser_Rank@1x.png


Hope it resolves your issue? 
Did I answer your question? Mark my post as a solution!

Appreciate your Kudos, Press the thumbs up button!!
Linkedin Profile
Anonymous
Not applicable

Hi @pranit828 ,

Thanks again.

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.

 

Best Regards,

Dedmon Dai

pranit828
Community Champion
Community Champion

HI @Anonymous 

Yopu need to edit your interactions on the table. Check the lin k below.

PBI Interactions 

 

 





PBI_SuperUser_Rank@1x.png


Hope it resolves your issue? 
Did I answer your question? Mark my post as a solution!

Appreciate your Kudos, Press the thumbs up button!!
Linkedin Profile
Anonymous
Not applicable

@pranit828  thanks for the response.

 

I want the filters to work, but show me the rows where filter is not applicable as null values.
for an example when i apply perfromance and ptg filter(table2) to the report, then show me the all metrics i.e. metric 1 ,2, 3 and 4
but only metric1 should have values(derived value) rest should be blank/null and when i remove filters then show all the respective values(derived value).

 

 

vanessafvg
Super User
Super User

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.





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Anonymous
Not applicable

Thanks for the response.

 

here is my calculation

 

var CurrDate1 =
CALCULATE (
MAX ( Merge1[metric_date]),
//FILTER ( ALL ( archer_kri_latest_summary), 'archer_kri_latest_summary'[metric_date] = MAX ( archer_kri_latest_summary[metric_date] ) )
ALLSELECTED ( 'Merge1' )
)
var b = CALCULATE (
AVERAGE(Merge1[derived_value]),
FILTER(Merge1,month(Merge1[metric_date])=MONTH(CurrDate1)&&YEAR(Merge1[metric_date])=YEAR(CurrDate1))
)/100
return b
 
I want the filters to work, but show the rows where filter is not applicable as null values.
 
for an example when i apply perfromance and ptg filter(table2) to the report, then show me the all metrics i.e. metric 1 ,2, 3 and 4
but only metric1 should have values(derived value) rest should be blank/null and when i remove filters then show all the respective values(derived value).
 
 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.