Visualize Column values without blanks in Matrix

[Arthemis_Runner]

PowerBI might not be the solution for this, but I was tasked with the challenge and I was drawing blanks for options. 

I have the following sample table:

ID	Team	Item	YrQtr	%Complete
1	X	A1	2023-Qtr1	100
2	X	A2	2023-Qtr1	75
4	X	B	2023-Qtr1	75
3	X	A3	2023-Qtr2	25
5	Y	C	2023-Qtr2	50
6	Y	D	2023-Qtr3	25
7	Y	E	2023-Qtr4	0

The goal is to visualize the table above as:

2023-Qtr1	2023-Qtr2	2023-Qtr3	2023-Qtr4
✔️A1	🌔A3	🌔D	🌕E
🌒A2	🌓C
🌒B

With each value having a conditional icon depending on the %Complete

I have tried a few things:

Matrix: This will always aggregate the values column so only 1 value will be displayed. If the ID column is show, you will be left with blank values.

Pivoting the YrQtr Column: This will not be dynamic when new YrQtr values are inserted as the columns would have to be selected to be included. Also, you will experience the same issue as with the matrix when visualizing (blank values).

Also, cannot find any custom visuals that address this issue either.

[barritown · Accepted solution]

Hi @Arthemis_Runner,

I could propose such a solution. 

Firstly, you create a new table with the help of the [DAX] code below:

Then using the matrix visual you create a visualisation alike:

Here's the code in plain text for convenience:

Table = 
VAR RowsNumber = MAXX ( SUMMARIZE ( Data, [YrQtr], "Cnt", COUNT ( Data[ID] ) ), [Cnt] )
VAR ExtData = ADDCOLUMNS ( Data, 
                           "Rank", 
                           VAR CurQtr = [YrQtr]
                           VAR CurID = [ID]
                           RETURN COUNTX ( FILTER ( Data, [YrQtr] = CurQtr && [ID] <= CurID ), [ID] ),
                           "Output",
                           SWITCH ( TRUE(),
                                [%Complete] = 100, "✅",
                                [%Complete] = 75, "🌖",
                                [%Complete] = 50, "🌗", 
                                [%Complete] = 25, "🌘",
                                "🌚") & " " & [Item] ) 
RETURN ADDCOLUMNS ( CROSSJOIN ( SELECTCOLUMNS ( GENERATESERIES ( 1, RowsNumber, 1 ), "Row", [Value] ), VALUES ( Data[YrQtr] ) ), 
                    "Value", 
                    VAR CurQtr = [YrQtr]
                    VAR CurRow = [Row]
                    RETURN MINX ( FILTER ( ExtData, [Rank] = CurRow && [YrQtr] = CurQtr ), [Output] ) )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian