Conditional formatting in Row matrix based on certain values

[masterelaichi]

Hi,

I have a matrix visual where I am trying to create a measure to display a specific value of column

The scenario:
1. I have a table with three columns CaseID, Date and Review Status

2. The cases are reviewed each day, which means the same CaseID can have multiple dates with a review status

I am trying to create a row matrix with the CaseID and Date in the Row field and review status in the values field. I want to show the status of the Review Status to be "Complete" only when all the review status of each date is "Complete". Instead what I get is the first value alphabetically

I couldn't attach the pbix file so I have copied the M code and pasted it here. As you can see C1 and C2 are incorrect as it is showing "Complete" even though their sub-rows have different review statuses. C3 is correct as all the sub-rows are "Complete"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdHBCoMwDAbgVyk9C7Zp1Xn2tMuQXUV2MYiwtUNle32FbRCEpbkF/i85/Ok63dyszjTkxuVgwOzzJa7qiq8J3zjoPvsRlyaekE+smvh43nFFogqRKkWqEqkTUeeg2jmOMy4LEXVSWMMSODTUYhimMJLUs2mRvF6y+xX/HTi08IfUyTrdtwovYFbGQMacjHmO9Rs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CaseID = _t, Date = _t, ReviewStatus = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"Review Complete","Complete",Replacer.ReplaceText,{"ReviewStatus"})
in
    #"Replaced Value"

Thanks in advance!

 

[C Moore]

What if you put ReviewStatus as columns, moved dates from rows to values as a count? It wouldn't show "Complete" but you would know it is if zeros were in all ReviewStatus except Complete.