cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Dsharma43
Helper II
Helper II

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

 

8 REPLIES 8
Dsharma43
Helper II
Helper II

@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 @Dsharma43 

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

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 @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.

 

Best Regards,

Dedmon Dai

pranit828
Super User II
Super User II

HI @Dsharma43 

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

@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 I
Super User I

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors