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
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.
Solved! Go to 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")
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" ) ...
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" ) ...
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 ,
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 @Anonymous
Just in response to the items you noted:
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
@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
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
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.
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 🙂
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
Hi @Anonymous
You can use the following measure:
Measure =
VAR _1 = COUNTROWS ( 'Table' )
RETURN
SWITCH (
TRUE() ,
_1 < 2 , "No" ,
"Yes" )
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
User | Count |
---|---|
115 | |
94 | |
87 | |
76 | |
65 |
User | Count |
---|---|
138 | |
113 | |
110 | |
98 | |
93 |