help calculated column or measures help

[majid154a]

Dear Experts,

There is a company that relies on 3 certificates for its clients to approve their request entirely. The certificates are A, B, and C, and they are approved sequentially. In the table, I have three columns: the first column is the customer number, and the number is repeated according to the three certificates. The second column indicates the certificate name, and the third column indicates whether the certificate is approved or not.

So, I want to extract three results for each certificate: whether it is approved or not. It should be noted that approval must be in order for the three certificates. For example, if certificate B is approved, it means that certificate A is approved, and also if certificate B is not approved, it means that certificate C is not approved. And so on.

The attached table illustrates an example of the data with the results, but I would like to know how to do it in DAX.

Thanks for help

ID	Certificate	Approval
1	A	TRUE
2	A	FALSE
3	A	TRUE
4	A	TRUE
1	B	TRUE
2	B	FALSE
3	B	TRUE
4	B	FALSE
1	C	TRUE
2	C	FALSE
3	C	FALSE
4	C	FALSE

If I take slicer for ID=1, it shows A=Approved, B=Approved, C=Approved

                            ID=2, it shows A= Not Approved, B=Not Approved, C=Not Approved

                          ID= 3, it shows, A=Approved, B=Approved, C= Approved

                           ID=4, it shows, A=Approved, B=Not Approved C= Not Approved

[lbendlin]

ID 3 is not approved.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIE4pCgUFelWJ1oJSOogJujTzBExBhdiQm6AMgQJ3RDnDAMcUI3BFUJyBRndFOcMUxBFTFBFYkFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Certificate = _t, Approval = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,each [Approval],each if [Approval]="TRUE" then 1 else 0,Replacer.ReplaceValue,{"Approval"}),
    #"Grouped Rows" = Table.Group(#"Replaced Value", {"ID"}, {{"Overall", each List.Product(_[Approval])}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Grouped Rows",{{"Overall", type logical}})
in
    #"Changed Type"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.