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.

amitchandak's avatar
amitchandak
Super User
5 years ago

Anonymous , In power bi , you need join two tables on vw_PasF_BookingJourney  vw_PasD_Category  using

[Category ID] and [Actual Category],

 

On slicer you can take filter of [Booking Reference] = "21394547"

 

Or add this measure with all un summarized columns

 

calculate(countrows(vw_PasF_BookingJourney ), filter(vw_PasD_Category , vw_PasD_Category[Booking Reference] = "21394547"))

 

refer my series DAX vs SQL -https://www.youtube.com/watch?v=WlvQ_SGy4iA&list=PLPaNVDMhUXGZNyKU0PgG2g3P0c6CPjMnj