Matrix Visual: How to filter out blank data that only appear after based on slicer condition

[rouelandrew]

I have a Matrix Table with the below data. What I'm trying to show is which units were below SLA for X consequtive months.

The potential users should be able to filter by Account Name, input their targetted SLA and choose which months to look at, in this case it's from January up to the current month of April. The blank fields showing up for the month columns are not blank in the actual dataset, but are those months where that unit had an SLA above what was set.

Example: If X unit had an SLA of 100% for 2024-01, the report would show that month blank. But if you set the SLA to 100% then the data shows up.

Since the client wants to know which units were failing SLA for X # of consequtive months, the current version of the report doesn't give them that straight answer. Instead it shows them all the units that failed within that time span, including those that only failed for 1 month and just puts in the passing SLA as blank.

The workaround right now is to Export the table to Excel and the filter out the blanks there. But is there a way to do that within Power BI?

[Anonymous]

Hi, @rouelandrew 

Perhaps you can use DAX to create a new column in the dataset to flag SLA failures. When the SLA is not met, this column can have a value of 1, otherwise 0.

SLA_Failure_Flag = IF([SLA_Percentage] < [Target_SLA], 1, 0)

Apply a filter on the matrix visual to include only records that are 1. This will automatically exclude all "blank" instances that represent SLA achievements.

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data)

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rouelandrew]

Hi, I think this would work. The problem is the SLA is not necessarily set. As mentioned, there is a field where the user/client can input the SLA that they want to target. Let's say for example they change the SLA target to something like 99.5%, the matrix table needs to be able to reflect that. One of the filters/slicers is a free text field where they can input that desired SLA. Is there a way using DAX that I can indicate that whatever % SLA or number is in that slicer, that is what will be used to calculate the SLA Failure Flag in the calculated column?

[rouelandrew]

Hi, I think this would work. The problem is the SLA is not necessarily set. As mentioned, there is a field where the user/client can input the SLA that they want to target. Let's say for example they change the SLA target to something like 99.5%, the matrix table needs to be able to reflect that. One of the filters/slicers is a free text field where they can input that desired SLA. Is there a way using DAX that I can indicate that whatever % SLA or number is in that slicer, that is what will be used to calculate the SLA Failure Flag in the calculated column?