Forum Discussion

majid154a's avatar
majid154a
Icon for Helper II rankHelper II
2 years ago

help calculated column or measures help

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

 

IDCertificateApproval
1ATRUE
2AFALSE
3ATRUE
4ATRUE
1BTRUE
2BFALSE
3BTRUE
4BFALSE
1CTRUE
2CFALSE
3CFALSE
4CFALSE

 

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

1 Reply

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    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.