Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

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.

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

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 

View solution in original post

Anonymous
Not applicable

 

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 

View solution in original post

11 REPLIES 11
Anonymous
Not applicable

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
Not applicable

 

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
Not applicable

Hi TheoC,

 

Thanks for helping. I ran the query you posted but got an error. Please see snip below:

sunnybi1_0-1643852581592.png

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
Not applicable

Hi @TheoC ,

Thanks for helping. I ran the query you posted but got an error. Please see snip below:

sunnybi1_1-1643852974512.png

 

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

TheoC_1-1643853296849.png

 

Hope this helps and all the best mate.

Theo 🙂

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

TheoC
Super User
Super User

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

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Anonymous
Not applicable

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.



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.

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Anonymous
Not applicable

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:

sunnybi1_0-1643859889420.png

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.

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

TheoC_0-1643866008219.png

All the best with the journey!

Theo 🙂

 

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

TheoC
Super User
Super User

Hi @Anonymous 

 

You can use the following measure:

 

Measure = 

VAR _1 = COUNTROWS ( 'Table' )
RETURN
SWITCH (
TRUE() ,
_1 < 2 , "No" ,
"Yes" )
Output is per below:
 

TheoC_0-1643849597181.png

Hope this helps 🙂

Theo

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.