cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Kartiklal03
Frequent Visitor

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, [Category Price], [Booking Currency Price]

from vw_PasF_BookingJourney bf

join vw_PasD_Category c on bf.[Category ID] = c.[Actual Category]

where [Booking Reference] = '21394547'

 

Thanks in advance!

 

1 ACCEPTED SOLUTION
v-yingjl
Community Support
Community Support

Hi @Kartiklal03 ,

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

q1.pngq2.png

The sql query would result this result:

q3.png

 

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]
        )
)

tb.png

 

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.

View solution in original post

5 REPLIES 5
v-yingjl
Community Support
Community Support

Hi @Kartiklal03 ,

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

q1.pngq2.png

The sql query would result this result:

q3.png

 

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]
        )
)

tb.png

 

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.

View solution in original post

Hi @v-yingjl ,

 

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

Kartiklal03_0-1618996297600.png

 

Hi @Kartiklal03 ,

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.

@Hi @v-yingjl , 

 

Thank you! Exactly what I wanted. 

amitchandak
Super User IV
Super User IV

@Kartiklal03 , 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



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