Forum Discussion

Smartnag's avatar
Smartnag
Frequent Visitor
4 years ago
Solved

In Statement in sql, same in power bi

Hi All,

 

Need help on below.

 

=Max([CD] In ([Bookinghour]) )  is in SQL.

 

Both CD and Bookinghour  are the columns.

 

Same how to achieve in Dax.

 

Thanks,

Nag

 

 

  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Smartnag,

    Did you mean to get the 'CD' value based on the max 'bookinghours', right?
    If that is the case, you can get the max 'bookinghours' first, then you can use this as a condition to find out the CD value.

    formula =
VAR maxHour =
    CALCULATE ( MAX ( Table[Bookinghour] ), ALLSELECTED ( Table ) )
RETURN
    CALCULATE (
        MAX ( Table[CD] ),
        FILTER ( ALLSELECTED ( Table ), [Bookinghour] = maxHour )
    )

    Regards,

    Xiaoxin Sheng

2 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi Smartnag 

    it depends on the filter context and the desired result. But in general it would be something like 

    = MAX ( [CD] ) IN VALUES ( [Bookinghour]) )

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Smartnag,

    Did you mean to get the 'CD' value based on the max 'bookinghours', right?
    If that is the case, you can get the max 'bookinghours' first, then you can use this as a condition to find out the CD value.

    formula =
VAR maxHour =
    CALCULATE ( MAX ( Table[Bookinghour] ), ALLSELECTED ( Table ) )
RETURN
    CALCULATE (
        MAX ( Table[CD] ),
        FILTER ( ALLSELECTED ( Table ), [Bookinghour] = maxHour )
    )

    Regards,

    Xiaoxin Sheng