Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
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
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
I still don't see any word that it should be stored, not displayed as column
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
73 | |
51 | |
45 | |
16 | |
12 |