Forum Discussion
Dynamic Radar Chart using date slicers
Hi
I have a main table (called TableExample) with many columns (about 100) and rows ( in thousands) but here are the needed columns I will be working with and a sample data:
| Date | HF30 | MI30 | ST30 | BL30 | STR30 |
| 24/01/2023 09:35 | 1 | 0 | 1 | 0 | 0 |
| 24/03/2023 09:35 | 0 | 1 | 0 | -1 | 0 |
| 24/05/2023 09:35 | 0 | 1 | 0 | 0 | 0 |
| 24/07/2023 09:35 | 1 | 0 | 0 | 0 | 1 |
| 24/08/2023 09:35 | 0 | -1 | -1 | 0 | -1 |
I'm trying to create a dynamic radar chart that uses the date as slicer (this date is already made as the slicer for all charts on the same Power BI page), and I want to use the 5 variables (HF30, MI30, ST30, BL30 and STR30) as the y-axis and the values are the total counts when each variable = 1 (do not count 0's and -1's).
I manged to create new measures for each variable to count all 1's:
I then union them together into a new table in model:
The Radar chart look like this (the values on this radar chart don't represent the ExampleTable of only 5 rows above):
The radar chart looks correct from what I need but, the values are displaying the total values of entire table (there are thousands of rows). When I change date slider, the radar chart does not update.
How can I make it work so that the radar chart changes whenever I slide the date slicer? The rest of my graphs works with the date slicer. I worked on this for days and still can't get it to work! So I will be very grateful if anyone can give me some advice please! Thank you!!!
Hi whgenie
By adding a date column (not datetime) to the source table, I can add a 1:* relationship between the date table and the source table. This way the source table visuals would react to the slicer like the unpivoted table visuals.
(The duplicate table has to be a duplicate of the source table - not a reference.)
Dynamic Radar Chart - test.pbix
Let me know if you have any questions.
13 Replies
Take your source data and unpivot it. That will make all following steps much easier.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLRNzDUNzIwMlYwsLQyNlXSUTIEYgMk2kApVgei0BhVIbIiXUMUlaa4VaIaaY7dbgO4JphCC0wTwXbqIrkgNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, HF30 = _t, MI30 = _t, ST30 = _t, BL30 = _t, STR30 = _t]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Date"}, "Attribute", "Value"), #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Value", Int64.Type}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Value] = 1)) in #"Filtered Rows"Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.Thanks lbendlin for replying!
I created a custom fuction and pasted your code. It then outputs this:
I guess it is because your code is not taking my original table as source but the fake sample data I entered above in this forum. I don't know how to change the source to my table from your coding, BUT even if I managed to change to my original table as source, the table outcome from your code is not what I'm after. I'm after something like this that Radar chart can take as inputs to work (using my fake sample data given above):
Notice that all -1's and 0's are not counted.
Does a Date column needs to be included too? Currently the radar chart is not updating whenever I move my date slicer (or maybe I am using the Radar chart incorrectly?).
Thank you for looking into my problem.
My code's output is different.
All you need to do is swap out my Source = line and replace it with your source.
