Forum Discussion

TJK's avatar
TJK
Helper I
1 year ago
Solved

Group By Calculated Column

How would I create the "Is Loyal v2" column in the Table View using DAX based on the first two columns? I want the outcome to equal "No" for all rows if there is a row that equals "No" for the ID in the first column.

 

I know I can achieve this with Power Query, but wondering if it's possible in the table view.

 

IDIs LoyalIs Loyal v2
1YesNo
1YesNo
1YesNo
1NoNo
1YesNo
2YesNo
2NoNo
2YesNo
2YesNo
2YesNo
3YesYes
3YesYes
3YesYes
3YesYes
3YesYes
calculated column
DAX
Group By
  • jgeddes's avatar
    jgeddes
    1 year ago

    As a calculated column...

    Is Loyal v2 = 
CALCULATE(
    MIN([Is Loyal]),
    ALLEXCEPT(loyalTable, loyalTable[ID])
)

4 Replies

  • ahadkarimi's avatar
    ahadkarimi
    Solution Specialist
    1 year ago

    Hi TJK, go to Modeling ->New column

     

    Is Loyal v2 = 
IF(
    CALCULATE(
        COUNTROWS('Table'),
        'Table'[ID] = EARLIER('Table'[ID]),
        'Table'[Is Loyal] = "No"
    ) > 0,
    "No",
    "Yes"
)

     

     

    Did I answer your question?  If so, please mark my post as the solution!✔️
    Your Kudos are much appreciated!  Proud to be a Responsive Resident!

    • TJK's avatar
      TJK
      Helper I
      1 year ago

      Hi ahadkarimi ,

       

      You're formula is giving me the same result as the "Is Loyal" column. It's only showing "No" when "Is Loyal" is equal to "No".  I would like it to show "No" for all rows for the given ID.

      • jgeddes's avatar
        jgeddes
        Super User
        1 year ago

        As a calculated column...

        Is Loyal v2 = 
CALCULATE(
    MIN([Is Loyal]),
    ALLEXCEPT(loyalTable, loyalTable[ID])
)