Show all Categories in Bar Chart after applying Page level Filter

[LilianTan]

Hi ,

I am creating a visual that required me to show all of the categories even that categories has no "Count".

I tried the "Show item with no data" but it is still not working as I would like it to.

Data:

I have a column of "school ID"

A column of "category" of the score

A column of "Legislative ID"

Visual:

y-axis : The count of the schools in certain category

x-axis: The categories available. (I want to list all 5 categories)

Page Level filter:

Legislative ID

What I have now: 

When I made a selection of Legislative ID. The bar chart only show the categories that certain district has. Sometimes it is two, three or four.

Desired outcome:

When I make a selection of Legislative ID. I would like the Bar Chart to show the Count of schools in the district selected AND Still "show all of the x-axis Categories" even though that Legislative district does not have school falls in that category.

[v-frfei-msft · Accepted solution]

Hi @LilianTan,

I made one sample for your reference. Please check the following steps as below.

1. Create a calculated table and create the relationship between it and the fact table, please make the relationship inactive.

Table = VALUES(data[category])

2. Create a measure as below. Then we can get the result as we excepted.

Measure = var countsc = CALCULATE(COUNT(data[school ID]),USERELATIONSHIP(data[category],'Table'[category]))
return
IF(ISBLANK(countsc),0,countsc)

For more details, please check the pbix as attached.

Regards,

Frank

[LilianTan]

Thank you so much for the help. 

I really learned something useful from your post.