New Column, SWITCH & AND dax formula not working

[pramita]

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: 

IMDD CATEGORY = SWITCH(

TRUE(),

AND(

[JUNIOR COUNT] - CALCULATE(

[JUNIOR COUNT],

OUTPUT[Ratings])< 4,

[JUNIOR COUNT]

),"INSPIRED",

AND([MID COUNT] - CALCULATE(

[MID COUNT],

OUTPUT[Ratings]) < 4,

[MID COUNT]),"INSPIRED",

AND([SENIOR COUNT] - CALCULATE(

[SENIOR COUNT],

OUTPUT[Ratings]) < 4,

[SENIOR COUNT]),"INSPIRED",

"NOT INSPIRED"

)

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

[amitchandak]

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

[AntrikshSharma]

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/

[harshnathani · Accepted solution]

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

[pramita]

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

https://docs.google.com/spreadsheets/d/1hHKXhP-ZVbdV6GMv-KOtXLn65oVlizgkjzChgNip9m0/edit?usp=sharing 

[pramita]

@harshnathani Thanks for replying

I am attaching the data link for further reference. 
https://docs.google.com/spreadsheets/d/1hHKXhP-ZVbdV6GMv-KOtXLn65oVlizgkjzChgNip9m0/edit?usp=sharing 

There 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?