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 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 ConfigTarget ConfigCompare ValueFinal Value
101102NoYes
101102NoYes
101102YesYes
101102YesYes
110120NoNo
110120NoNo
110120NoNo
110120NoNo
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.

3 Replies

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