Forum Discussion

patkang's avatar
patkang
Icon for Helper I rankHelper I
5 years ago
Solved

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:

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

 

Need Help
Show and Tell
Tips and Tricks
  • FrankAT's avatar
    FrankAT
    5 years ago

    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)

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    patkang , Try a measure like


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

  • FrankAT's avatar
    FrankAT
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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's avatar
      patkang
      Icon for Helper I rankHelper I
      5 years ago

      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's avatar
        FrankAT
        Icon for Community Champion rankCommunity Champion
        5 years ago

        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)