Forum Discussion
Dynamic Radar Chart using date slicers
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.
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.
Hi lbendlin
gmsamborn had private messaged and trying to help me. I've downloaded the .pbix file which has your codes and had a look. Some issues:
1. The radar values are wrong. It sums everything up so if there are -1 values, it actually minus off the totals. I want to count 1 only and ignore 0 and -1.
2. The problem with unpivoting is that it will affect my original table which has many other data that are being used. So what I did was:
- I duplicated my orginal table to a new table as a reference to the orginal table
- Now using the new table, I removed all other columns except the Date, HF30, MI30, ST30, STR30, and BL30.
- Then unpivot as Ibendin did = Table.UnpivotOtherColumns(#"Removed Other Columns", {"DOP"}, "Attribute", "Value")
- Then to replaced all -1 values with 0 (now the table only has 0s and 1s)
- Created Radar chart, dragged and dropped Attribute into Category and Sum of Value into Y Axis
The radar chart output the entire table's sum of the individual Attributes (all date range of the entire table from 2013 to 2023). Now, when I use my slicer to show only July 2023, the radar chart still does not update and still shows entire sum of values of 2013 - 2023.
When I click on the radar chart's filter, it says correctly that the year has been applied when I changed the slicer date but the values remain the same:
How do I make the date slicer (which is sourced from my original table) work on my radar chart?
