Slicer selection not working for line

[simrantuli]

Hi All,

I have 3 tables in my data model. Screenshot below.

Basically, this is a survey data where Q1 & Q2 were two questions. People from various sites participated in this survey. Data of the 3 tables below:

Q1:

Q2:

Site_Summary:

Now, the requirement is to create a Line and Clustered column chart for each question with 'Global' represented by a Line and the rest of the sites represented by Bars. Measure being the sum of Response_Count. I was able to achieve it using the below DAX.

DAX for Line: CALCULATE(SUM(Q1[Response_Count]),Q1[Site]="Global")

DAX for Bars: CALCULATE(SUM(Q1[Response_Count]),Q1[Site]<>"Global")

My chart for Q1 looks like below with 'Site' from 'Site_Summary' table under Column Series and 'Response' from Q1 table as Shared axis. So far so good.

The problem comes when I select 'Global' from the slicer. My chart goes BLANK.

However, it works fine when another site is selected along with 'Global'. Screenshot below.

What could be the reason? How do I solve this?

I have also given the link below to download the PBIX file.

https://drive.google.com/file/d/1Qf9n6DP4fgJ9BDSNhu5sfdTxRiXWtX9N/view?usp=sharing 

You help would be greatly appreciated.

[simrantuli · Accepted solution]

Thank you so much @camargos88  & @sfaizee for the answers.

I was able to fix this by checking 'Show items with no data' for 'Response' under Shared axis.

[camargos88]

@simrantuli ,

That's because you measure doesn't return anything for column values when you only select Global:

Q1_Response_Site = CALCULATE(SUM(Q1[Response_Count]),Q1[Site]<>"Global")

[simrantuli]

Hi @camargos88,

Thanks for the answer.

Is there any way to make it work? I want to see the line if 'Global' is selected.

Thanks!

[camargos88]

@simrantuli ,

Try this measure:

Q1_Response_Site = CALCULATE(SUM(Q1[Response_Count]),Q1[Site]<>"Global") + 0

[simrantuli]

Hi @camargos88,

Thanks. It works.

However, I see 'Global' twice in the legend now. One for Bar, whose value is 0 now, and the other for Line.

Any way to get rid of the extra Legend value?

Thanks!

[camargos88]

@simrantuli ,

Doesn't work for you if you just rename the line value like "Global - Line" ?

[simrantuli]

@camargos88 

It will create confusions.

[sfaizee]

The 2 globals are showing becuase there is a value in your table named global. 

try changing the graph type and it will be fixed. 

Or you can filter it out.