Forum Discussion

Arthemis_Runner's avatar
Arthemis_Runner
Frequent Visitor
3 years ago
Solved

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...
Need Help
  • barritown's avatar
    barritown
    3 years ago

    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

barritown's avatar
barritown
Solution Sage
3 years ago

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