Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
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")
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!
Try this measure:
Q1_Response_Site = CALCULATE(SUM(Q1[Response_Count]),Q1[Site]<>"Global") + 0
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!
Doesn't work for you if you just rename the line value like "Global - Line" ?
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
77 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |