Using group by function in dax

[Anonymous]

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

[v-lionel-msft · Accepted 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.

[Anonymous]

Hi Lionel,

It works. Thanks. But can you also explain the DAX to me? I'm not a regular user of advanced DAX functions.

[v-lionel-msft]

Hi @Anonymous ,

First, return a table by grouping two columns: [Anchor Config] & [Target Config], the function I used is "EARLIER()":

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()":

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