New column crossed mapping - DAX

[Anonymous]

Hi,

I have a question about making an additional column based on the combination of values in two other columns.

The data looks like this:

Attributes	ID	Status
Attribute A	X1	Pass
Attribute A	X2	Pass
Attribute A	X3	Pass
Attribute B	Y1	Fail
Attribute B	Y2	Fail
Attribute C	Z1	Pass
Attribute C	Z2	Pass
Attribute C	Z3	Pass
Attribute C	Z4	Fail

The mapping is based on data from two different files: “Attributes” is extracted from File 1 and the other two columns are being extracted from File 2. This means that there is only 1 “Attribute A” (or B or C) but its being shown multiple times due to the mapping between the files.

However, now I want to add a fourth column based on the combination of the values in column “Attributes” and “Status”. If “Status” includes at least one “Fail”, the new status (fourth column) should be “Fail”. This would be the case for Attribute B and C in this example. That means in the end the data will look like this:

Attributes	ID	Status	Fourth column
Attribute A	X1	Pass	Pass
Attribute A	X2	Pass	Pass
Attribute A	X3	Pass	Pass
Attribute B	Y1	Fail	Fail
Attribute B	Y2	Fail	Fail
Attribute C	Z1	Pass	Fail
Attribute C	Z2	Pass	Fail
Attribute C	Z3	Pass	Fail
Attribute C	Z4	Fail	Fail

Or when a different mapping is being done, like this:

Attributes	Fourth column
Attribute A	Pass
Attribute B	Fail
Attribute C	Fail

Can anyone help me with creating the correct DAX for this?

Thanks in advance!

[Anonymous]

If your table is big, this code blows away code that uses context transition out of water:

[Fourth Column] =
var __currentAttr = YourTable[Attribute]
var __failExists =
	not isempty(
		filter(
			YourTable,
			YourTable[Attribute] = __currentAttr,
			YourTable[Status] = "fail"
		)
	)
return
	if(__failExists, "Fail", "Pass")

Best

D

[az38]

Hi @Anonymous 

try a measure

Fourth column = 
var _isFail = CALCULATE(COUNTROWS(Table), ALLEXCEPT(Table, Table[Attributes]), Table[Status] = "Fail")
RETURN
IF(_isFail > 0, "Fail", "Pass")

[Anonymous]

@az38, he wanted a calculated colum, not a measure...

Secondly, on big tables creating calc columns with CALCULATE is a bad idea. Very bad. This is because you are invoking context transition potentially millions of times. THis will be agonizingly slow.

Best
D

[az38]

@Anonymous why are you sure he wants a column? Users often confused with columns and measures. It really not so easy to feel the difference. And, technically, measure looks like a column too 🙂

[Anonymous]

If you carefully read the initial post, you'll know why I'm sure he wants a calc column.

Best
D

[az38]

@Anonymous 

I still don't see any word that it should be stored, not displayed as column

[Anonymous]

Please read the first sentence of the post. Paired with the rest of it (especially the talk about FILES), it gives a very strong hint it should be a calculated column.

If that's still not convincing, then... well, I can't say I'm too much worried about it 🙂 Let's move on.

Best
D