Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

SQL to DAX

Hello All,    I need some help in converting the following SQL query into a DAX measure    select [Booking Reference], [Sailing ID], [Sailing Date], [Category ID], c.[Category Group], Quantity, [...
Need Help
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    Hi Anonymous ,

    Based on your description, I have create two sample tables in sql and in power bi desktop:

    The sql query would result this result:

     

    To get the same result table in power bi, you can create this caculated table:

    Result table =
ADDCOLUMNS (
    FILTER (
        ALL ( 'BF' ),
        'BF'[Booking Reference] = "21394547"
            && 'BF'[Category ID] IN DISTINCT ( 'C'[Actual Category] )
    ),
    "Category Group",
        MAXX (
            FILTER ( 'C', 'C'[Actual Category] = 'BF'[Category ID] ),
            [Category Group]
        )
)

     

    Attached the sample file in the below, hopes it could help.

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi v-yingjl ,

 

Thanks for the explanation. However, I am getting the following error when I try to create the calculated table. 

 

v-yingjl's avatar
v-yingjl
Community Support
5 years ago

Hi Anonymous ,

Seems like your data type of [Booking Reference] is number type not text type while my sample file is the text type. You can modify it like this:

 'BF'[Booking Reference] = 21394547

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    @Hi v-yingjl , 

     

    Thank you! Exactly what I wanted.