Forum Discussion
Visualize Column values without blanks in Matrix
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.
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
1 Reply
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