Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
stevenmcginnis
Frequent Visitor

Creating a pivot table with icons in place of numbers.

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:

 

Desired outcome.Desired outcome.

 

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:

 

ProjectTeamTypeStage
Project 1Team 1Type 1Stage 1
Project 2Team 1Type 2Stage 2
Project 3Team 1Type 3Stage 3
Project 4Team 2Type 1Stage 4
Project 5Team 2Type 2Stage 1
Project 6Team 2Type 3Stage 2
Project 7Team 3Type 1Stage 3
Project 8Team 3Type 2Stage 4
Project 9Team 3Type 3Stage 1
Project 10Team 4Type 1Stage 2
Project 11Team 4Type 2Stage 3
Project 12Team 4Type 3Stage 4
Project 13Team 1Type 3Stage 2
Project 14Team 2Type 2Stage 1
Project 15Team 2Type 2Stage 1
Project 16Team 3Type 2Stage 1
Project 17Team 4Type 2Stage 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.

1 ACCEPTED SOLUTION
v-easonf-msft
Community Support
Community Support

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" )
    )

veasonfmsft_0-1668156625597.png

https://unicode-table.com/en/1F7E6/ 

https://unicode-table.com/en/1F7E8/ 

https://unicode-table.com/en/1F7E5/ 

 

Best Regards,
Community Support Team _ Eason

View solution in original post

4 REPLIES 4
v-easonf-msft
Community Support
Community Support

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" )
    )

veasonfmsft_0-1668156625597.png

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:

  • Is it possible to wrap the squares? I imagine when projects within an intersection reach a high enough number it might present problems.
  • Is it possible to set all columns to be the same width?
  • Some of the totals in my graph have turned into squares, what is likely to have gone wrong? It appears to happen when a row or column only has one value. Is there a way around this?
  • In your square measure what does "General Number" do?

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.

veasonfmsft_0-1669974906126.png

2. Maybe you can increase the height from Visualizations>>FORMAT Pane>>Grid>>Row padding

veasonfmsft_1-1669975418043.png

 

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.