cancel
Showing results for
Did you mean:
Helper I

## Conditional output based on other DAX

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:

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

1 ACCEPTED SOLUTION
Super User II

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)

5 REPLIES 5
Super User II

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)

Helper I

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

Super User II

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)

Helper I

@FrankAT  you're awesome, thanks a lot!

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

Super User IV

@patkang , Try a measure like

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

Proud to be a Super User!

Announcements

#### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks