help calculated column or measures help

majid154a — Wed, 22 May 2024 08:05:42 GMT

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

Re: help calculated column or measures help

lbendlin — Wed, 22 May 2024 20:29:19 GMT

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.

topic Re: help calculated column or measures help in DAX Commands and Tips

help calculated column or measures help

Re: help calculated column or measures help