Forum Discussion

Lakhani04's avatar
Lakhani04
Frequent Visitor
1 year ago
Solved

Assistance Required - Power BI Visual Matrix

Hello Everyone, I hope you're doing well. I have a quick query regarding a Power BI matrix visualization. I’m trying to create a table with the following setup: Rows: Assignment Group Columns: Y...
Need Help
  • antfr99's avatar
    antfr99
    1 year ago

    Hi Tejas

     

    Ok, great to hear that there is some progress. I took another look to see if I could get rid of the empty columns . I could only find a solution in Power Query and this was the result : 

     

     

     

     

    In general the steps taken are as follows :  Identifies the Maxmonth and two months before ( Query : MaxMonthQuery  ) , which joins with the Grouped average per assignment group ( Query : GroupedAverageQuery ) . This then finally joins with the MaindataQuery ( filtered to show only 3 months prior to max date of the data ) to merge the average SLA% per group.

     

    For the updated file see the same folder I placed the other Power BI files in. 

     

    Hope this helps

    Antonio 

     

     

antfr99's avatar
antfr99
Resolver II
1 year ago

HiLakhani04 

 

Thanks for the extra information , below is an amended measure : 

 

SLA % Last 3M (Only Latest Month) = 
VAR MaxDate =
    CALCULATE(MAX('Data'[Date]), ALL('Data'))
VAR MaxMonth =
    EOMONTH(MaxDate, 0)
VAR CurrentMonth =
    EOMONTH(MAX('Data'[Date]), 0)
VAR Last3Months =
    DATESINPERIOD(
        'Data'[Date],
        MaxDate,
        -3,
        MONTH
    )
RETURN
    IF (
        CurrentMonth = MaxMonth,
        CALCULATE(
            AVERAGE('Data'[SLA %]),
            Last3Months,
            ALLEXCEPT('Data', 'Data'[Assignment Group])
        )
    )

 

The formula finds the latest month in the data and if the context is the latest month it calculates the average SLA % over the last 3 months ( else it remains blank ) 

 

The header of this new measure is a bit of challenge. I selected Specific column in formatting options and and changed the text to white so that it does not appear for each month. I then added text below below the table to indicate what the last column shows. 

 

 

Below is the amended file. 

Amended File 

Hope this is closer to what you require

Antonio 

 

Lakhani04's avatar
Lakhani04
Frequent Visitor
1 year ago

Hi Antonio,

Thank you so much for the updated file and the revised DAX. I truly appreciate the workaround you’ve implemented—it's a clever solution!


That said, I’m looking for a more permanent fix where the Matrix table doesn’t generate that empty column at all. If there’s a way to eliminate it entirely at the source, that would be ideal.


Thanks again for your support and great work on this!


Rgards,
Tejas Lakhani

  • antfr99's avatar
    antfr99
    Resolver II
    1 year ago

    Hi Tejas

     

    Ok, great to hear that there is some progress. I took another look to see if I could get rid of the empty columns . I could only find a solution in Power Query and this was the result : 

     

     

     

     

    In general the steps taken are as follows :  Identifies the Maxmonth and two months before ( Query : MaxMonthQuery  ) , which joins with the Grouped average per assignment group ( Query : GroupedAverageQuery ) . This then finally joins with the MaindataQuery ( filtered to show only 3 months prior to max date of the data ) to merge the average SLA% per group.

     

    For the updated file see the same folder I placed the other Power BI files in. 

     

    Hope this helps

    Antonio 

     

     

    • Lakhani04's avatar
      Lakhani04
      Frequent Visitor
      1 year ago

      Thanks a lot Antonio, this solution helps.