Display all distinct values in table (ignore page filter)

[BIStarter]

Hi everyone,

I am trying to figure out if it's possible to display all distinct values from column, also those that does not meet page filter requirements and display for them value 0.

All the solutions I've found contained creating a new table and it's impossible in the direct query mode I am using.

Source data looks like this :

Number	Organization	Rate
1	UK	Good
2	GER	Bad
3	UK	Good
4	NED	Very Bad
5	UK	Good
6	UK	Good
7	UK	Very Bad

And the filter at the page level filters only rate Good and Bad (excluding very bad).

I would like to show in the table all organizations (also those that have only very bad rating) and display the number 0 for them so the table after applying page filters will look like this :

Organization	Count of Number
UK	4
GER	1
NED	0

Can anyone give me some hint on how to achieve that without creating new table as it's impossible?

[Anonymous · Accepted solution]

Thanks for the reply from hackcrr  and bhanu_gautam , please allow me to provide another insight:

Hi  @BIStarter ,

Are you referring to filtering “very bad” in the page-level filters, and then showing the filtered data as 0 in the table visual?

As far as I know, page-level filters directly filter the entire page and remove the filtered data from the visual. This is by design in Power BI. You might consider creating a new table [Rate] without any relationships as a slicer and using a measure to achieve this.

Here are the steps you can follow：

1. Create calculated table.

Table 2 =
DISTINCT('Table'[Rate])

2. Create measure.

Measure =
var _select=CONCATENATEX(ALLSELECTED('Table 2'),'Table 2'[Rate],"-")
var _table=
FILTER(
   ALLSELECTED('Table'),CONTAINSSTRING(_select,'Table'[Rate])=TRUE())
var _count=
COUNTX(
    FILTER(_table,[Organization]=MAX('Table'[Organization])),[Number])
return
IF(
    _count=BLANK(),0,_count)

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[hackcrr]

Hi, @BIStarter 

Create two measures using the following two DAX expressions:

CountOfNumber = 
CALCULATE(
    COUNT('Table'[Number]),
    'Table'[Rate] IN {"Good", "Bad"}
)

CountOfNumberWithZero = 
IF(
    ISBLANK([CountOfNumber]),
    0,
    [CountOfNumber]
)

Create a table visual object and put CountOfNumberWithZero into the visual object

If this post helps, then please consider Accept it as the solution and kudos to this post to help the other members find it more quickly

[BIStarter]

Thank you very much, can you also advise how to handle that in case if more filters on the page level would be added? 

[bhanu_gautam]

@BIStarter , You can try creating measure 

CountOfNumber =

   CALCULATE(

       COUNT('YourTable'[Number]),

       'YourTable'[Rate] IN {"Good", "Bad"}

   )

And one for 

CountWithZero =

   IF(

       ISBLANK([CountOfNumber]),

       0,

       [CountOfNumber]

   )

Add a table visualization to your report and include the Organization column and the CountWithZero measure.

Ensure All Organizations are Displayed: To ensure that all organizations are displayed, you might need to adjust the table's interactions or use a slicer to include all organizations.