Using a new column, compile a yes or no value for column A based on values in Column B

[Anonymous]

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.

[Anonymous · Accepted solution]

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")

@amitchandak 

[Anonymous · Accepted solution]

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")

@amitchandak 

[Anonymous · Accepted solution]

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" ) ...

@ValtteriN 

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[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 🙂

[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 🙂

[Anonymous]

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.

[Anonymous]

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 me

Please 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
_2

All the best with the journey!

Theo 🙂

[TheoC]

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