Show a single row for combination of columns of a matrix visual

[Nikhil_567]

Hi All,

I have situation where I need to show the matrix visual like below-

For each unique comibnation of Market, Data Source & Refresh date, if any of the status is Fail, then the matrix visual should show just the row with Fail status.

Please help.

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[v-nuoc-msft]

Hi @Nikhil_567 

Thank you very much bhanu_gautam for your prompt reply.

I am sure bhanu_gautam's reply can help you to solve your problem, here allow me to share something.

Here's some dummy data

“Table”

Create a measure.

Measure Status = 
IF(
    CALCULATE(COUNTROWS('Table'), 'Table'[Status] = "Fail") > 0,
    "Fail",
    "Pass"
)

Here is the result.

Regards,

Nono Chen

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

[bhanu_gautam]

@Nikhil_567 , First Drag the Market, Data Source, and Refresh Date fields to the Rows area of the Matrix visual.
Add the Status field to the Values area.

You need to create a measure that checks if any of the statuses for a combination of Market, Data Source, and Refresh Date is "Fail". You can use the following DAX formula to create this measure:

DAX
FailStatus =
IF(
HASONEVALUE('YourTable'[Market]) &&
HASONEVALUE('YourTable'[Data Source]) &&
HASONEVALUE('YourTable'[Refresh Date]),
IF(
CALCULATE(
COUNTROWS('YourTable'),
'YourTable'[Status] = "Fail"
) > 0,
"Fail",
"Pass"
),
BLANK()
)

Drag the FailStatus measure to the Values area of the Matrix visual.

Apply a visual-level filter to the Matrix visual to only show rows where FailStatus is "Fail".

[Nikhil_567]

Hi @bhanu_gautam 

Thanks for taking out time to help me out here!!

However, if we apply a visual level filter on the matrix. I believe it will only show the failed records.

Please note that, if all the status is Pass for the combination of the mentioned column, then it should show as Pass.

I forgot to mention that.

[bhanu_gautam]

Create a measure to determine the status:

DAX
FailStatus =
IF(
HASONEVALUE('YourTable'[Market]) &&
HASONEVALUE('YourTable'[Data Source]) &&
HASONEVALUE('YourTable'[Refresh Date]),
IF(
CALCULATE(
COUNTROWS('YourTable'),
'YourTable'[Status] = "Fail"
) > 0,
"Fail",
"Pass"
),
BLANK()
)

Drag the Market, Data Source, and Refresh Date fields to the Rows area of the Matrix visual.

Add the FailStatus measure to the Values area of the Matrix visual.

Apply a visual-level filter to the Matrix visual to only show rows where FailStatus is not blank.