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 the below table In which Final Value is my output column
It is supposed to be calculated such that if
Case 1: For Each Anchor Config and Target Config Combination, If Compare value has at least 1 Yes value then all rows become Yes.
Case 2: For Each Anchor Config and Target Config Combination, If Compare value has no Single Yes value then all rows become No
Anchor Config | Target Config | Compare Value | Final Value |
101 | 102 | No | Yes |
101 | 102 | No | Yes |
101 | 102 | Yes | Yes |
101 | 102 | Yes | Yes |
110 | 120 | No | No |
110 | 120 | No | No |
110 | 120 | No | No |
110 | 120 | No | No |
Solved! Go to Solution.
Hi @Anonymous ,
Here’s my DAX, which is a column:
F_value =
VAR x =
FILTER(
Sheet4,
[Anchor Config] = EARLIER([Anchor Config]) && [Target Config] = EARLIER([Target Config])
)
VAR y =
CONTAINS(
x,
[Compare Value],
"Yes"
)
RETURN
IF(
y = TRUE(),
"Yes",
"NO")
And here’s my test result:
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Here’s my DAX, which is a column:
F_value =
VAR x =
FILTER(
Sheet4,
[Anchor Config] = EARLIER([Anchor Config]) && [Target Config] = EARLIER([Target Config])
)
VAR y =
CONTAINS(
x,
[Compare Value],
"Yes"
)
RETURN
IF(
y = TRUE(),
"Yes",
"NO")
And here’s my test result:
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Lionel,
It works. Thanks. But can you also explain the DAX to me? I'm not a regular user of advanced DAX functions.
Hi @Anonymous ,
First, return a table by grouping two columns: [Anchor Config] & [Target Config], the function I used is "EARLIER()":
Second, determine if there is a 'yes' in the table (the table is the first step's return ), the function I used is "CONTAINS()":
At last, use IF function to return the column.
If you understand the 'Context' in DAX, it will be easy.
Row context & filter context
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
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 |
---|---|
111 | |
94 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |