Forum Discussion
Helper IINew Column, SWITCH & AND dax formula not working
Hi all,
I have been trying to make a New Column called "IMDD Category" with a switch formula that categorizes it into "INSPIRED" or "NOT INSPIRED". The formula is as such:
We need to see if the following rating is less than 4, then see if the employee is in the Junior/Mid/Senior Level. If he is at the Junior level and has less than 4 ratings, he is inspired. If he is at the middle and the senior, then he is inspired too. However, if the ratings are more than 4, he is uninspired. I know there is data in my table that counts as uninspired. But when I check it, there is no uninspired data after this formula as shown in the below picture
Kindly help me know where I am going wrong and correct me.
Thanks and Regards,
Pramita
Hi pramita ,
not very clear from your table.
What does measure Junior Count do ? Can you share some sample data.
Also, You can try
SWITCH(
TRUE(),
OUTPUT[Ratings])< 4 && Output[Status] = "Junior", "Inspired",
OUTPUT[Ratings])< 4 && Output[Status] = "Middle", "Inspired",
OUTPUT[Ratings]) > 4 , "Uninspired"
)
Regards,
Harsh Nathani
5 Replies
Hi pramita ,
not very clear from your table.
What does measure Junior Count do ? Can you share some sample data.
Also, You can try
SWITCH(
TRUE(),
OUTPUT[Ratings])< 4 && Output[Status] = "Junior", "Inspired",
OUTPUT[Ratings])< 4 && Output[Status] = "Middle", "Inspired",
OUTPUT[Ratings]) > 4 , "Uninspired"
)
Regards,
Harsh Nathani
pramitaAnonymous Thanks for replying
I am attaching the data link for further reference.
https://docs.google.com/spreadsheets/d/1hHKXhP-ZVbdV6GMv-KOtXLn65oVlizgkjzChgNip9m0/edit?usp=sharingThere are 4 measures, INSPIRED, MOTIVATED, DISENCHANTED and DETACHED.
I need to make a column and categorize it into the above 4 values.
I am also listing down my measures:
COUNT = DISTINCTCOUNT('OUTPUT'[Respondent ID])JUNIOR COUNT = CALCULATE([COUNT],INPUT[Level] = "JR")MID COUNT = CALCULATE([COUNT],INPUT[Level] = "MID")SENIOR COUNT = CALCULATE([COUNT],INPUT[Level] = "SR")INSPIRED JUNIOR COUNT = [JUNIOR COUNT] - CALCULATE([JUNIOR COUNT],OUTPUT[Ratings] < 4)INSPIRED MID COUNT = [MID COUNT] - CALCULATE([MID COUNT],OUTPUT[Ratings] < 4)INSPIRED SR COUNT = ([SENIOR COUNT] - CALCULATE([SENIOR COUNT],OUTPUT[Ratings] < 4))INSPIRED % = DIVIDE(([INSPIRED JUNIOR COUNT] + [INSPIRED MID COUNT] + [INSPIRED SR COUNT]),[COUNT])MOTIVATED JUNIOR COUNT = ([JUNIOR COUNT] - CALCULATE([JUNIOR COUNT],'OUTPUT'[Questions] IN {"Q1", "Q5", "Q7", "Q8", "Q11"},'OUTPUT'[Ratings] < 4)) - [INSPIRED JUNIOR COUNT]MOTIVATED MID COUNT = ([MID COUNT] - CALCULATE([MID COUNT],'OUTPUT'[Questions] IN {"Q1", "Q5", "Q7", "Q6", "Q11", "Q14"},'OUTPUT'[Ratings] < 4)) - [INSPIRED MID COUNT]MOTIVATED SENIOR COUNT = ([SENIOR COUNT] - CALCULATE([SENIOR COUNT],'OUTPUT'[Questions] IN {"Q1", "Q5", "Q7", "Q8", "Q11", "Q2", "Q13", "Q14", "Q15"},'OUTPUT'[Ratings] <= 3)) - [INSPIRED SR COUNT]MOTIVATED % = DIVIDE(( [MOTIVATED JUNIOR COUNT] + [MOTIVATED MID COUNT] + [MOTIVATED SENIOR COUNT]),[COUNT])Disenchanted JR table =SUMMARIZECOLUMNS('OUTPUT'[Respondent ID],'OUTPUT'[Email Address],FILTER('INPUT','INPUT'[Level]="JR"),FILTER('OUTPUT','OUTPUT'[Questions] IN {"Q1", "Q11", "Q5", "Q7", "Q8"}),FILTER('OUTPUT','OUTPUT'[Ratings] < 4),"Question Count", COUNT('OUTPUT'[Questions]))Disenchanted Junior Counts = (CALCULATE(COUNTROWS('Disenchanted JR table'),'Disenchanted JR table'[Question Count] < 3))Disenchanted MID table =SUMMARIZECOLUMNS('OUTPUT'[Respondent ID],'OUTPUT'[Email Address],FILTER('INPUT','INPUT'[Level]="MID"),FILTER('OUTPUT','OUTPUT'[Questions] IN {"Q1", "Q11", "Q5", "Q6", "Q7", "Q14"}),FILTER('OUTPUT','OUTPUT'[Ratings] < 4),"Question Count", COUNT('OUTPUT'[Questions]))Disenchanted Middle Counts = (CALCULATE(COUNTROWS('Disenchanted MID table'),'Disenchanted MID table'[Question Count] < 4) )Disenchanted SR table =SUMMARIZECOLUMNS('OUTPUT'[Respondent ID],'OUTPUT'[Email Address],FILTER('INPUT','INPUT'[Level]="SR"),FILTER('OUTPUT','OUTPUT'[Questions] IN {"Q1", "Q11", "Q5", "Q7", "Q8", "Q2", "Q13", "Q14", "Q15"}),FILTER('OUTPUT','OUTPUT'[Ratings] < 4),"Question Count", COUNT('OUTPUT'[Questions]))Disenchanted Senior Counts = (CALCULATE(COUNTROWS('Disenchanted SR table'),'Disenchanted SR table'[Question Count] < 5) )DISENCHANTED % = DIVIDE(([Disenchanted Junior Counts] + [Disenchanted Middle Counts] + [Disenchanted Senior Counts]),[COUNT])DETACHED % = 1 - ([INSPIRED %] + [MOTIVATED %] + [DISENCHANTED %])I need the Inspired, Motivated, Disenchanted and Detached categories in one column. Is it possible?- pramita
Anonymous thanks for replying,
Junior count is a measure that counts how many people are In the junior level
JUNIOR COUNT = CALCULATE([COUNT],INPUT[Level] = "JR")Here, count is the total number of People in the the output table.COUNT = DISTINCTCOUNT('OUTPUT'[Respondent ID])The link to the data is given below
- AntrikshSharma
Community Champion
I would suggest that you familiarize yourself with Context Transition and how filter arguments of CALCULATE works. you can refer to the below links
https://www.sqlbi.com/articles/understanding-context-transition/
https://www.sqlbi.com/blog/marco/2010/01/03/how-calculate-works-in-dax/ - amitchandak
Super User
pramita , formula ha issue. what does this do
CALCULATE(
[JUNIOR COUNT],
OUTPUT[Ratings])or
- CALCULATE(
[MID COUNT],
OUTPUT[Ratings])or
CALCULATE(
[SENIOR COUNT],
OUTPUT[Ratings])Can you share sample data and sample output in table format?
