Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Using group by function in dax

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 h...
Need Help
  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    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's avatar
Anonymous
Not applicable
6 years ago

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's avatar
v-lionel-msft
Community Support
6 years ago

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