Forum Discussion
Using Multiple Measures in A Visual (Spider/Radar Chart or any other visual besides table/matrix)
I have some industrial data with the table name 'December' consisting of many columns, but to simplify I have showed 3 columns below:
Here, the 'Source' denotes the name of the alarm, the 'TimeActive' is not important, and the 'Hour' denotes the hour of the day when the alarm occured. I need to display the data (basically the count of source) in visuals. For this, I have a slicer giving the user the option to select the range of Hours from which to choose to display the data for those particular hours.
The problem is, I want to display the data for the hours that the user has not selected, as well, and this should be displayed as zero. The reason for this is, that I am displaying the number of alarms on a spider chart/radar chart like this:
This works fine, when all the hours have been selected. However, when the user selects a range of hours like, for instance, 0-9, then the clock like structure of the radar chart is ruined as all the categories for all the hours not selected just disappear. I need a data structure or technique in which these categories do not disappear but instead show up as zero value on the chart. To address this, I have tried many things, including, using DAX as,
0_count = IF(ISBLANK(COUNTROWS(FILTER('December', December[Hour] == 0))), 0, COUNTROWS(FILTER('December', December[Hour] == 0)))
| Hour | Count |
| 0 | 0_count |
| 1 | 1_count |
| 2 | 2_count |
| 3 | 3_count |
But even this is not possible to do using any technique that I have come across.
Can someone please help me out or has some suggestions as to what to do? It seems Power BI has some excellent features, but always somehow manages to fall just short of perfection due to a missing feature.
HI Anonymous,
Please remove the measure on 'visual level filter' and use below measure formula to replace 'source' field which you used on Y axis:
Measure = IF ( MAX ( 'Alarms2019-1-1'[Hour] ) IN ALLSELECTED ( 'Table'[Value] ), 0, COUNT ( 'Alarms2019-1-1'[Source] ) )It will dynamically replace selected hours with zero and keep other 'count of source' not changes.
Regards,
Xiaoxin Sheng
7 Replies
- mahoneypat
Microsoft Employee
One approach is to make a simple DAX calculated table with something like
Hour = GenerateSeries(1,12,1) and use that Hour[Value] column in the visual
You can then write a measure like this
NewMeasure = Calculate([OriginalMeasure], TreatAs(Values[Hour[Value]), OriginalTable[Hour]) +0
That way the slicer on your original hour column won't also filter your visual "axis".
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
Thank you for replying. So I did try the method you suggested, however, the data does not get filtered dynamically. That is, when the user selects hour range 0-5, for example, the other hours (6-23) should show 0 on the radar chart. Instead the values remain fixed. I can achieve the same by just turning off slicer interaction with the radar chart.
I realize that calculated tables do not get refreshed on slicer selection, only on data refresh. So is there any way to get the above requirement working?
I have attached a pbix file if you want to play around with it.
Thanks a lot. Google Drive Link for PBIX File
- Greg_Deckler
Community Champion
Perhaps try using a disconnected table of hours. Use that in your radar chart. Construct your measure like:
VAR __Alarms =
SWITCH('DisconnectedHours'[Hour],
1,CALCULATE([Alarms Measure],'Table'[Hour]=1,
2,CALCULATE([Alarms Measure],'Table'[Hour]=2,
...
)
RETURN
IF(ISBLANK(__Alarms),0,__Alarms)If you could post as text, I (or others) could experiment with it.
Hi, thanks for replying. But I have the same problem as the previous solution. I did try the method you suggested, however, the data does not get filtered dynamically. That is, when the user selects hour range 0-5, for example, the other hours (6-23) should show 0 on the radar chart. Instead the values remain fixed. I can achieve the same by just turning off slicer interaction with the radar chart.
Hi Anonymous,
You can create a calculated table with 0 ~23 as the source of the slicer that not related to raw table records.
Then you can write a measure formula to compare selected value and raw table records to return tag and use it on visual level filter:Table = GENERATESERIES(0,23,1)Measure = IF ( MAX ( 'Alarms2019-1-1'[Hour] ) IN ALLSELECTED ( 'Table'[Value] ), "N", "Y" )Regards,
Xiaoxin SHeng
