Forum Discussion
Using a new column, compile a yes or no value for column A based on values in Column B
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" ) ...
Community ChampionAnonymous 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 A
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
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
Please see snip below:
Edgar, Ila is one of those employees is both a casual and adjunct instructor, hence she should appear as "Yes"But when I generated the measure and calculated column using the query you gave me, here is what I see:
NB: Measure on the table is the measure I generated with your query
Column is also the calculated column I generated with the query you gave mePlease advise. Thanks.
- TheoC
Hi Anonymous
Sorry mate. I didn't realise you wanted to filter using slicers. Adjust the MEASURE to the following please and your output will be what you're after. We just need to add a CALCULATE and FILTER ALL to it like follows:
Measure =
VAR _1 = COUNTROWS ( 'Table' )
VAR _2 = CALCULATE ( SWITCH ( TRUE() , _1 < 2 , "No" , "Yes" ) , ALL ('Table' ) )
RETURN
_2All the best with the journey!
Theo 🙂