Forum Discussion
Using a new column, compile a yes or no value for column A based on values in Column B
Hi Everyone,
I need to generate a new column in my report using DAX. This new column should contain a yes or no values for Column A based on the values in Column B.
For clarity and with reference to the sample table below, I want to know the employers who work as both Adjunct and Casual Instructors.
Can someone help please?
Table example.
Employee names Position
------------------------------------
John Casual instructor
Jarius Casual instructor
Stella Adjunct Instructor
John Adjunct Instructor
Stella Casual Instructor
Dan Adjunct Instructor
Amy Permanent Instructor
Emily Casual Instructor
John Casual Instructor
Peter Permanent Instructor
Dan Casual Instructor
I would like the output to be:
Employee names Position Adjunct and Casual Instructor
---------------------------------------------------------------------------
John Casual instructor yes
Jarius Casual instructor no
Stella Adjunct Instructor yes
John Adjunct Instructor yes
Stella Casual Instructor yes
Dan Adjunct Instructor yes
Amy Permanent Instructor no
Emily Casual Instructor no
John Casual Instructor yes
Dan Casual Instructor yes
Peter Permanent Instructor No
Dan Casual Instructor yes
Your assistance will be much appreciated
Thanks.
Try new column
new column =
var _cnt = calculate(distinctCOUNT(Table[Position]), Filter(Table, [Employee names] = earlier([Employee names]) && Table[Position] in {"Casual instructor", "Adjunct Instructor"} ))
return
if( not(isbalnk(_cnt)) && _cnt =2, "Yes", "No")Another Solution:
Hi, Here is an example on how to do this: Positions = var inspect = Inspector[Name] return If ( COUNTROWS ( FILTER ( ALL ( Inspector ), Inspector[Name] = inspect &&( Inspector[Position] = "A" || Inspector[Position] = "B" )))= 2 , "Yes" , "No" ) ...
11 Replies
- TheoC
Community Champion
Hi Anonymous
You can use the following measure:
Measure =
VAR _1 = COUNTROWS ( 'Table' )
RETURN
SWITCH (
TRUE() ,
_1 < 2 , "No" ,
"Yes" )Output is per below:Hope this helps 🙂
Theo
- TheoC
Anonymous apologies. I only provided you with a measure solution. If you are strictly after a Calculated Column, please use the below:
Calculated Column =
VAR _1 = CALCULATE ( COUNTROWS ( 'Table' ) , FILTER ('Table' , 'Table'[Name] = EARLIER ( 'Table'[Name] ) ) )
RETURN
SWITCH ( TRUE () , _1 > 1 , "Yes" , "No" )All the best!
Theo 🙂
Hi TheoC ,
Thank you so much. Really appreciate your efforts.
Both your measure and calculated column queries are working but when I place then in a visual and then make analysis to confirm if same people indicated as both adjunct and casual instructors in your queries match with those in the data I am using, then it doesnt seem to be right.
I wish there's a way to show you if ok by you.
However, for clarity, I don't know which syntax in the query represents the "table name" and which one represnts the "column" please advise or clarify.
Here's the table again. Lets assume the table name is AI would like the output to be:
Employee names Position Adjunct and Casual Instructor
---------------------------------------------------------------------------
John Casual instructor yes
Jarius Casual instructor no
Stella Adjunct Instructor yes
John Adjunct Instructor yes
Stella Casual Instructor yes
Dan Adjunct Instructor yesAmy Permanent Instructor no
Emily Casual Instructor no
John Casual Instructor yes
Dan Casual Instructor yes
Peter Permanent Instructor No
Dan Casual Instructor yes
Please, what will be the right query for a "calculated column" or "measure" referencing the columns in the above table?
Your explanation will be highly appreciated.
- TheoC
Hi Anonymous
Wherever I have put 'Table' in my measure or column, change "Table" to your table name.
For the Measure, you don't need to worry about the column names, only the Table name.
Hope this helps.
Theo
PS take a screenshot of the issues with your output and I can try assist from that.
Hi TheoC,
Thanks for helping. I ran the query you posted but got an error. Please see snip below:
A few observations,
-there's a reference to the table but there's no reference to the columns
- Is this a query for a "measure" or can I use this query in Power Query (ie Calculated column)
I would prefer a "measure" to avoid increasing data size which slows reports but please advise.
Thanks,
Sunny
Hi TheoC ,
- TheoC
Hi Anonymous
Just in response to the items you noted:
- COUNTROWS is counting the rows on a specific table so it won't use the Column given that the table and the column will always have the same number of rows (hope that makes sense).
- This version is for a Measure. I posted a separate one for a Column.
- You need to use "_1" underscore _ not the "-" regarding the _1. The error is basically saying that it can't identify the "expression" -1. The reason for this is that the VAR has been named _1 and not "-1".
Hope this helps and all the best mate.
Theo 🙂
Try new column
new column =
var _cnt = calculate(distinctCOUNT(Table[Position]), Filter(Table, [Employee names] = earlier([Employee names]) && Table[Position] in {"Casual instructor", "Adjunct Instructor"} ))
return
if( not(isbalnk(_cnt)) && _cnt =2, "Yes", "No")Another Solution:
Hi, Here is an example on how to do this: Positions = var inspect = Inspector[Name] return If ( COUNTROWS ( FILTER ( ALL ( Inspector ), Inspector[Name] = inspect &&( Inspector[Position] = "A" || Inspector[Position] = "B" )))= 2 , "Yes" , "No" ) ...
