Drill Down with correction for blank & filtering

[MartasPD]

Hello,

I am trying to solve a combination of settings in my report (sourced by csv files on SharePoint).

I have several sourcing files, sorted per date - eg 1 query for Incidents opened, 1 query for Incidents closed, another for Problems opened & closed, etc. to be able to use only 1 filter I created a new table with dates & linked relationships accordingly. I did similar activity with Product name table & Product group (several levels / organised 1 level per table).

The aim was not only to use one date filter for all queries and one Product filter, but also to be able to drilldown. 

Then I needed to correct the "known" issue with Blank by using either solution (Measure) + 0   or IF (Measure) = blank then 0  else (Measure).

for example: Inc_Closed_All = COUNTROWS(DISTINCT(Data_Inc_Closed[Incident Reference Number])) +0

Both solutions have caused that during drilling I see records with Product (Affected service) that do not comply with selected filter on a slicer (synchronised across pages). They show up in drilling with 0 values.
I would like those records not to be visible.

If I remove the fix for the "known" issue on blank, the report shows "nice & clean" drilling.

Thanks for any idea.

Martina

Drilling (Not OK) with products that do not comply with selected product group on a slicer

[v-shex-msft · Accepted solution]

Hi @MartasPD,

AFAIK, add zero operation will replace all blank value as zero so it will appears on charts.

If you want to display them in table/matrix but not show on charts, you only need to add visual level filter on value column to filter zero values and blank value.

Regards,

Xiaoxin Sheng

[MartasPD]

hmmm 🙂  

Thank you @v-shex-msft

I was thinking of that solution, yet was affraid to lose figures in other columns. I could not make my mind up, whether the filtering is will or will not have some negative impact.   either on performance or hidden data...

Right now I am thinking of having two sets of measures:

- 1 for matrix & graphs :  measure123=countrows(......)

- 2 for cards :     measureAB=measure123 + 0

I dont like it much, but visually it would probably solve my problem 😞  ...