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.
I am attempting to create a "portfolio at a glance" visualization. Essentially, what I am trying to create is a pivot table with a count of square icons instead of numbers for each intersection between "Team" & "Stage" and colored by a "Value" category, like below:
I have gotten as far as overlaying a table with a rept measure to convert numbers to icons, the problem with this is that I had to create 3 cards for every intersection between Team & Stage (one for each value category), on top of each other. The problem with this is that it doesn't exactly match the image above as the different value categories don't lay next to each other but on top and also there is now over 100 independent cards making up the graph, which is resource intensive.
Some of the problems I am encountering are:
Icons don't wrap, they continue to move horizontally so after a certain number of icons an ellipsis is seen after the icons.
Conditional formatting with color measures don't work, as Power BI is preferring to create a conditional column in place of a measure, this just colors all icons with the first color. This is the reason I have not used multi-row cards.
The data I have looks something like this:
Project | Team | Type | Stage |
Project 1 | Team 1 | Type 1 | Stage 1 |
Project 2 | Team 1 | Type 2 | Stage 2 |
Project 3 | Team 1 | Type 3 | Stage 3 |
Project 4 | Team 2 | Type 1 | Stage 4 |
Project 5 | Team 2 | Type 2 | Stage 1 |
Project 6 | Team 2 | Type 3 | Stage 2 |
Project 7 | Team 3 | Type 1 | Stage 3 |
Project 8 | Team 3 | Type 2 | Stage 4 |
Project 9 | Team 3 | Type 3 | Stage 1 |
Project 10 | Team 4 | Type 1 | Stage 2 |
Project 11 | Team 4 | Type 2 | Stage 3 |
Project 12 | Team 4 | Type 3 | Stage 4 |
Project 13 | Team 1 | Type 3 | Stage 2 |
Project 14 | Team 2 | Type 2 | Stage 1 |
Project 15 | Team 2 | Type 2 | Stage 1 |
Project 16 | Team 3 | Type 2 | Stage 1 |
Project 17 | Team 4 | Type 2 | Stage 1 |
I am relatively new to Power BI so I'm not sure if there is a more obvious way for me to get to my desired graph that I have overlooked. The idea would be to automate the process so mapping it onto a scatter plot or some other type of plot with numbers is likely out of the question.
My questions would be:
Are there anything alternative solutions I have not considered, or any obvious mistakes I have made?
Are there any work arounds for the problems I mentioned above regarding color conditioning and measures, and icons wrapping?
Any help would be greatly appreciated, and apologies if I have asked this question incorrectly or missed any useful information.
Solved! Go to Solution.
Hi, @stevenmcginnis
Please try function 'Unichar' and 'Rept' in Dax.
Sample:
square =
VAR _type1 =
CALCULATE (
COUNT ( 'Table'[Type] ),
FILTER ( 'Table', 'Table'[Type] = "Type 1" )
)
VAR _type2 =
CALCULATE (
COUNT ( 'Table'[Type] ),
FILTER ( 'Table', 'Table'[Type] = "Type 2" )
)
VAR _type3 =
CALCULATE (
COUNT ( 'Table'[Type] ),
FILTER ( 'Table', 'Table'[Type] = "Type 3" )
)
VAR _quare =
REPT ( UNICHAR ( "128998" ), _type1 ) & REPT ( UNICHAR ( "128997" ), _type2 )
& REPT ( UNICHAR ( "129000" ), _type3 )
RETURN
IF (
HASONEVALUE ( 'Table'[Stage] ),
IF (
HASONEVALUE ( 'Table'[Team] ),
_quare,
FORMAT ( COUNT ( 'Table'[Type] ), "General Number" )
),
FORMAT ( COUNT ( 'Table'[Type] ), "General Number" )
)
https://unicode-table.com/en/1F7E6/
https://unicode-table.com/en/1F7E8/
https://unicode-table.com/en/1F7E5/
Best Regards,
Community Support Team _ Eason
Hi, @stevenmcginnis
Please try function 'Unichar' and 'Rept' in Dax.
Sample:
square =
VAR _type1 =
CALCULATE (
COUNT ( 'Table'[Type] ),
FILTER ( 'Table', 'Table'[Type] = "Type 1" )
)
VAR _type2 =
CALCULATE (
COUNT ( 'Table'[Type] ),
FILTER ( 'Table', 'Table'[Type] = "Type 2" )
)
VAR _type3 =
CALCULATE (
COUNT ( 'Table'[Type] ),
FILTER ( 'Table', 'Table'[Type] = "Type 3" )
)
VAR _quare =
REPT ( UNICHAR ( "128998" ), _type1 ) & REPT ( UNICHAR ( "128997" ), _type2 )
& REPT ( UNICHAR ( "129000" ), _type3 )
RETURN
IF (
HASONEVALUE ( 'Table'[Stage] ),
IF (
HASONEVALUE ( 'Table'[Team] ),
_quare,
FORMAT ( COUNT ( 'Table'[Type] ), "General Number" )
),
FORMAT ( COUNT ( 'Table'[Type] ), "General Number" )
)
https://unicode-table.com/en/1F7E6/
https://unicode-table.com/en/1F7E8/
https://unicode-table.com/en/1F7E5/
Best Regards,
Community Support Team _ Eason
That's amazing, thank you.
I had been doing something similar with the rept function and unichar but didn't realise they could be passed through as values in the matrix table.
Just some follow up questions if you don't mind:
Again thank you, you have no idea how long I've been trying to figure this out.
Hi. @stevenmcginnis
1.I think the text wraps automatically when you resize the column width.
2. Maybe you can increase the height from Visualizations>>FORMAT Pane>>Grid>>Row padding
3. I am sorry for my oversight. Here we need to use ISINSCOPE rather than HASONEVALUE in formula.
Reference:
https://www.sqlbi.com/articles/distinguishing-hasonevalue-from-isinscope/
square1 =
VAR _type1 =
CALCULATE (
COUNT ( 'Table'[Type] ),
FILTER ( 'Table', 'Table'[Type] = "Type 1" )
)
VAR _type2 =
CALCULATE (
COUNT ( 'Table'[Type] ),
FILTER ( 'Table', 'Table'[Type] = "Type 2" )
)
VAR _type3 =
CALCULATE (
COUNT ( 'Table'[Type] ),
FILTER ( 'Table', 'Table'[Type] = "Type 3" )
)
VAR _square =
REPT ( UNICHAR ( "128998" ), _type1 ) & REPT ( UNICHAR ( "128997" ), _type2 )
& REPT ( UNICHAR ( "129000" ), _type3 )
RETURN
IF (
ISINSCOPE( 'Table'[Stage] )&&ISINSCOPE('Table'[Team]),
_square,
FORMAT ( COUNT ( 'Table'[Type] ), "General Number" )
)
4. Just used to format the string, make sure the total value is displayed with no formatting
https://learn.microsoft.com/en-us/dax/format-function-dax#format-strings
Best Regards,
Community Support Team _ Eason
That's amazing, thank you again!
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |