cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LilianTan Frequent Visitor
Frequent Visitor

Show all Categories in Bar Chart after applying Page level Filter

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.

Show Item with no data.JPG

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.

Show Item with no data-I don't want this.JPG

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.

 

desired2.jpg

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Show all Categories in Bar Chart after applying Page level Filter

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

Capture.PNG

 

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)

2.PNG

 

For more details, please check the pbix as attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support Team
Community Support Team

Re: Show all Categories in Bar Chart after applying Page level Filter

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

Capture.PNG

 

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)

2.PNG

 

For more details, please check the pbix as attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
LilianTan Frequent Visitor
Frequent Visitor

Re: Show all Categories in Bar Chart after applying Page level Filter

Thank you so much for the help. 

I really learned something useful from your post.