CODEWITHSUNDEEP
excelexcel-2013daxcalculated-columnspowerquery
Seva Arve
Seva Arve

Reputation: 65

Calculated column based on subgroup condition

I need a solution based on DAX:

My Table:

Date       User       Code         Column1     
1/1/17     Karl       A            
1/1/17     Karl       B            
1/1/17     Joan       G            
1/1/17     Joan       B            
1/1/17     Maria      Z            
1/1/17     Maria      H            
1/1/17     Maria      F

How to create a loop or function in DAX, where I fill rows in Column1 based on a few conditions:

For each date in [Date] and for each User in [User] if the value "A" or "Z" exists in column [Code], then put "AUTO" in [Column1], else if the value "A" or "Z" does not exist in [Code] then put "NOT AUTO" in [Column1] like below:

My Table:

Date       User       Code         Column1     
1/1/17     Karl       A            Auto
1/1/17     Karl       B            Auto
1/1/17     Joan       G            NOT Auto
1/1/17     Joan       B            Not Auto
1/1/17     Maria      Z            Auto
1/1/17     Maria      H            Auto
1/1/17     Maria      F            Auto
....       .....      .....        .....

Is possible in DAX to solve this?

Note: As per discussion below, I need the DAX to function in Excel 2013, so newer functions may not be available.

Upvotes: 1

Views: 209

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

Try the following (assuming your table is named Users):

Column1 = 
    VAR AllCodes = CALCULATETABLE(VALUES(Users[Code]), ALL(Users[Code]))
    RETURN IF("A" IN AllCodes || "Z" IN AllCodes, "Auto", "NOT Auto")

The variable AllCodes calculates the set of all codes associated with the user and date in each row. (Note that I use CALCULATETABLE since I want to return multiple values.)

Using this, we test if either A or B is in AllCodes and assign Auto or NOT Auto accordingly.

If you can't use variables, try substituting the variable definition into the equation:

Column1 = IF("A" IN CALCULATETABLE(VALUES(Users[Code]), ALL(Users[Code])) ||
             "Z" IN CALCULATETABLE(VALUES(Users[Code]), ALL(Users[Code])),
             "Auto", "NOT Auto")

If the IN syntax doesn't work in your old version of DAX, then try CONTAINS like I suggested:

Column1 = IF(CONTAINS(CALCULATETABLE(VALUES(Users[Code]),ALL(Users[Code])), [Code], "A") ||
             CONTAINS(CALCULATETABLE(VALUES(Users[Code]),ALL(Users[Code])), [Code], "Z"),
             "Auto", "NOT Auto")

All three of these solutions reproduce your sample data in the latest version of Power BI desktop.

Power BI

Edit:

Upon reflection, it occurred to me that if there are additional columns, this may not work as expected since there will be additional row contexts to be considered. If there are additional columns, you probably want to use ALLEXCEPT(Users, Users[Date], Users[User]) instead of ALL(Users[Code]). This will remove all row context except the ones explicitly specified (Date and User) instead of only removing the Code row context.

Upvotes: 2

Related Questions