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:
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.
You help would be greatly appreciated.
Solved! Go to Solution.
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")
Thanks for the answer.
Is there any way to make it work? I want to see the line if 'Global' is selected.
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?
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.
We are excited to announce the Power BI Super Users!
Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI
Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!
100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.