Conditional output based on other DAX

[patkang]

Dear Community

I am trying to create a text output if a countrow function produces a certain output. 

Consider the table below. It shows a list of positions that potential nominees will take over at some point in time. 

Depending whether a position has 4 nominees with high intelligence, I would like the DAX to produce an output of "strong nominees" for example. 

I summarized it in the matrix visual, while the Dax formula is simply:

Nomination Team Leader= 
Countrows(Table1)

So depending whether this countrow output produces 4 times high intelligence people, I would like to create another DAX that would state that the position is well staffed. If the position has <2 high intelligence nominees, it is a risky one. 

Anyone with a solution to this? I would appreciate it a lot! Thank you in advance

[FrankAT · Accepted solution]

@patkang ,

with your sample data I get the following solution:

Nominees = 
VAR CountHighIntelligence = CALCULATE(COUNTROWS('Table'), 'Table'[Intelligence Id] = "High")
RETURN
    IF(CountHighIntelligence < 2 , 
    "risky" , 
        IF(CountHighIntelligence >= 4 ,
         "Strong nominess" , "not risky"
        )
    )

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[patkang]

@FrankAT 
Thanks a lot, They don't call him super user for no reason 🙂

On top of that, if I want to count the number of strong, risky or not risky positions. What measure could I apply for this? In that case it would be 3 times 1. But if i would have more than 3 positions and would want to aggregate the number of those positions, what would be the best way to do it?

[FrankAT]

Hi @patkang ,

in this case you should use the SWITCH() function, example

Nominees with SWITCH = 
VAR CountHighIntelligence = CALCULATE(COUNTROWS('Table'), 'Table'[Intelligence Id] = "High")
RETURN
    SWITCH(TRUE(),
        CountHighIntelligence = 1 , "try again",
        CountHighIntelligence = 2 , "nice try",
        CountHighIntelligence = 3 , "correct",
        CountHighIntelligence = 4 , "well done",
        "Best Hiro"
    )

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[patkang]

@FrankAT  you're awesome, thanks a lot!

It is my first time using Switch() and it works really great. 

[amitchandak]

@patkang , Try a measure like

if( calculate(Countrows(Table1), filter(Table,Table[Intelligence] = "High"), allexcept(Table, Table[positionID])) <=2 , "risky", "Non Risky")