If it's a specific value in one column, Return the value from the corresponding column

[katto16]

I have this kind of data table that consists of the test result readings together with the result pass or fail. I'd like to acheive something like

IF [Test 1 P/F] = "fail"

Return [Test 1] 

I'm not sure it's possible to be done in DAX? Another issue is that I have about 70 test parameters so there are gonna be about 70 columns x 2.

What I currently have is the unpivoted columns of 70 "Test P/F". Then I created a column called "Failure Mode"

Failure Mode = IF('DataUnpivot'[Value]="Fail",'DataUnpivot'[Attribute],BLANK())

 Then I created a calculated table,

Failure Types = ADDCOLUMNS(SUMMARIZE('DataUnpivot','DataUnpivot'[Serial no.],'DataUnpivot'[Position]),"Failure Type",
CALCULATE(CONCATENATEX('DataUnpivot','DataUnpivot'[Attribute]," ")))

 which returns something like this. Serial Numbers with the column name that has the value "fail"

But as I mentioned above, I want the value of the "Test  " when the "Test  P/F" is "fail". Thanks for any help possible. Appreciate!

[v-jianboli-msft]

Hi @katto16 ,

Please check your table Oven_1212 Unpivot readings for anything missing.

Also here are two posts about this issue, you can refer to them.

Expression.Error: Evaluation resulted in a stack o... - Microsoft Power BI Community

Solved: Expression.Error: Evaluation resulted in a stack o... - Microsoft Power BI Community

Hope they can help you.

Best Regards,

Jianbo Li

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

[v-jianboli-msft · Accepted solution]

Hi @katto16 ,

You can achieve this by using Power Query.

Please try:

First create two tables: Table remove Test P/F columns and Unpivot Test columns, Table(2) Remove Test columns and unpivot Test P/F columns.

Then filter Table(2) and add a custom column, here is the M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc+7DoAgDEDRf2HW0hbqY3RxdicMjiZu/n8iNUZCkAluSU5KCIZMZ8iSZWRO12Uh0hGDSDq2/brSgUBzrh4BXSNjFwzXJD9IIWIpOtdIFV0tPiMQzXU/zky+pVv5Rirpa1JHAuPc2jLV8F8KSg3K9+u84lQI5dtXMd4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Position = _t, #"Test Dates" = _t, #"Serial no." = _t, #"Test 1" = _t, #"Test 1 P/F" = _t, #"Test 2" = _t, #"Test 2 P/F" = _t, #"Test 3" = _t, #"Test 3 P/F" = _t, #"Test 80" = _t, #"Test 80 P/F" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Position", Int64.Type}, {"Test Dates", type date}, {"Serial no.", type text}, {"Test 1", type number}, {"Test 1 P/F", type text}, {"Test 2", type number}, {"Test 2 P/F", type text}, {"Test 3", type number}, {"Test 3 P/F", type text}, {"Test 80", type number}, {"Test 80 P/F", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Test 1", "Test 2", "Test 3", "Test 80"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Position", "Test Dates", "Serial no."}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Value] = "Fail")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each Text.Start([Attribute],
Text.Length([Attribute])-4))
in
    #"Added Custom"

Output:

Then add a custom column in Table then use the custom column to filter Table, after that remove the custom column, now the Table only have the value of the "Test  " when the "Test  P/F" is "fail"

Here is the M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc+7DoAgDEDRf2HW0hbqY3RxdicMjiZu/n8iNUZCkAluSU5KCIZMZ8iSZWRO12Uh0hGDSDq2/brSgUBzrh4BXSNjFwzXJD9IIWIpOtdIFV0tPiMQzXU/zky+pVv5Rirpa1JHAuPc2jLV8F8KSg3K9+u84lQI5dtXMd4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Position = _t, #"Test Dates" = _t, #"Serial no." = _t, #"Test 1" = _t, #"Test 1 P/F" = _t, #"Test 2" = _t, #"Test 2 P/F" = _t, #"Test 3" = _t, #"Test 3 P/F" = _t, #"Test 80" = _t, #"Test 80 P/F" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Position", Int64.Type}, {"Test Dates", type date}, {"Serial no.", type text}, {"Test 1", type number}, {"Test 1 P/F", type text}, {"Test 2", type number}, {"Test 2 P/F", type text}, {"Test 3", type number}, {"Test 3 P/F", type text}, {"Test 80", type number}, {"Test 80 P/F", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Test 1 P/F", "Test 2 P/F", "Test 3 P/F", "Test 80 P/F"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Position", "Test Dates", "Serial no."}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Custom", each Table.RowCount(
Table.SelectRows(#"Table (2)",(x)=>x[Position]=[Position] and x[Custom]=[Attribute]))),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1)),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Custom"})
in
    #"Removed Columns1"

Final output:

Best Regards,

Jianbo Li

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

[katto16]

Hi @v-jianboli-msft Thanks for the solution. It seems to be working until this step
adding custom column in Table. My M code looks the same to you but it's returning me an error code "Expression.Error: Evaluation resulted in a stack overflow and cannot continue"

 #"Added Custom" = Table.AddColumn(#"Unpivoted Only Selected Columns", "Custom", each Table.RowCount(
Table.SelectRows(#"Oven_1212 Unpivot readings",(x)=>x[Oven Position]=[Oven Position] and 
x[Custom]=[Attribute])))
in
    #"Added Custom"

This is my M code.

This the Power Query adding custom column step.

Any idea what the problem could be?

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.