Dynamic Radar Chart using date slicers

[whgenie]

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:

HF30_Total = CALCULATE(COUNTROWS('TableExample'), 'TableExample'[HF30] = 1)

MI30_Total = CALCULATE(COUNTROWS('TableExample'), 'TableExample'[MI30] = 1)

ST30_Total = CALCULATE(COUNTROWS('TableExample'), 'TableExample'[ST30] = 1)

BL30_Total = CALCULATE(COUNTROWS('TableExample'), 'TableExample'[BL30] = 1)

STR30_Total = CALCULATE(COUNTROWS('TableExample'), 'TableExample'[STR30] = 1)

I then union them together into a new table in model:

SummaryTable =

UNION (

    ROW ( "Category", "HF30", "Total", [HF30_Total] ),

    ROW ( "Category", "MI30", "Total", [MI30_Total] ),

    ROW ( "Category", "ST30", "Total", [ST30_Total] ),

    ROW ( "Category", "BL30", "Total", [BL30_Total] ),

    ROW ( "Category", "STR30", "Total", [STR30_Total] )

)

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!!!

[lbendlin]

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.

[whgenie]

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.

[lbendlin]

My code's output is different.

All you need to do is swap out my Source = line and replace it with your source.

[whgenie]

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?

[lbendlin]

Did you follow my advice and unpivot the data?  Can you post a sample pbix?

[whgenie]

Yes I did (see above my steps) but I unpivoted on a duplicate table not orignal table. I'll remove some of sensitive data and post a sample pbix file in private message to you soon. Allow me some time. Thank you in advance for your help!

[lbendlin]

Your unpivoted table seems to work fine.

[whgenie]

Hi

You seemed to have removed my other charts and updated the date slicer to the date on the unpivoted table. That works however, that means all my other charts will not be updated with the date slicer. That is not what I want.

I need BOTH other charts AND this radar chart to be dynamic whenever new date slicer is applied. How do I do that?  Thank you!

[lbendlin]

I showed you how you can refactor your source. I won't be able to help you if you insist on fixing your original setup.

[whgenie]

Thank you for showing me. Your coding does solve a small part but as a Data Analyst, if having one working visual but broke all the rest of visuals on the Dashboard (or all visual works except one), then this is not a good dashboard I want to publish.

I'll use your code but then I will figure out how to make the date slicers work for all visuals from different tables.

Unless someone else have a solution for this, I'd be GREATLY indebted! Thank you so much for your help so far!

[gmsamborn · Accepted solution]

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.

[whgenie]

Hi @gmsamborn 

Thanks for your test.pbix! I saw what you did with the relationship so I created a new calendar table and linked the relationship to both the orginal table and the new attibute table. IT WORKED! You are a LEGEND! THANK YOU!!!

[gmsamborn]

Hi @whgenie 

You're welcome.  I'm glad it worked.