cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
patkang
Helper I
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. 

patkang_0-1614677918269.png

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

patkang_1-1614678026100.png

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

 

1 ACCEPTED SOLUTION
FrankAT
Super User II
Super User II

@patkang ,

with your sample data I get the following solution:

 

02-03-_2021_11-26-09.png

 

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)

View solution in original post

5 REPLIES 5
FrankAT
Super User II
Super User II

@patkang ,

with your sample data I get the following solution:

 

02-03-_2021_11-26-09.png

 

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)

View solution in original post

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

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)

@FrankAT  you're awesome, thanks a lot!

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

amitchandak
Super User IV
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")



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

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

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors