return all rows from table1 irrespective of filter applied

[Anonymous]

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_name	Level	Derivedvalue	Date	metric_identifier	PTG	Performance
Metric1	Information Security	2	10/30/2020	Mandatory	yes	yes
Metric1	Information Security	3	10/30/2020	Mandatory	yes	yes
Metric1	Information Security	4	9/30/2020	Mandatory	yes	yes
Metric2	Technology	1	10/30/2020	Indicative	yes
metric3	Technology	2	9/30/2020
metric4	Third Party Outsourcing		9/30/2019

Example :

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.

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

[davidwc]

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]

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

Screenshot2

[pranit828]

Hi @Anonymous 

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

[Anonymous]

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.

[v-deddai1-msft]

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]

HI @Anonymous 

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

PBI Interactions 

[Anonymous]

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

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.

[Anonymous]

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