cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Yggdrasill
Resolver III
Resolver III

Convert SQL query to DAX

I have this query in SQL

 

 

select count(*) as 'result' 
from Table1 T1 where 
(select count(*) 
from Table2 T2 where 
T2.Id = T1.Id and 
T2.column1 = '5' 
and T2.DateUpdated >= @fromdate 
and T1.DateUpdated < @todate 
and T2.Column3 not in (3) 
and T2.Column2 = '6') > 0	

 

 

How do I convert this to a DAX measure?

 

I'm looking for something like

 

 

 

 

1 ACCEPTED SOLUTION

Got it!

 

Measure= 
var _table = 
CALCULATETABLE (
    FILTER (
        Table1,
        CALCULATE (
            COUNTROWS ( Table2 ),
            Table2[Column1] = 6,
            Table2[Column2] = 5,
            Table2[Column3] <> 3
        ) > 0
    ),
    USERELATIONSHIP ( 'Calendar'[Date], Table1[DateUpdated] )
)
return
COUNTROWS( _table )

 

View solution in original post

3 REPLIES 3
mahoneypat
Super User IV
Super User IV

Please provide sample data with desired output to get a specific DAX expression suggestion.  FYI too that you can use your sql directly in the Advanced Options of your connection depending on your source.

Chris Webb's BI Blog: Query Folding And Writing Your Own SQL Queries In Power Query/Power BI/Excel G...

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Not what I'm looking for - I'm well aware of the native SQL usage and how to view native queries that you build with Applied steps in PQ... 

I'm just not that good at DAX but I need it for this task.

Looking at the SQL script you can see I'm using two tables T1 and T2. There's a relationship between them. I also have in my Power BI Model a Calendar Table that I'm using instead of @fromdate and @todate.


Got it!

 

Measure= 
var _table = 
CALCULATETABLE (
    FILTER (
        Table1,
        CALCULATE (
            COUNTROWS ( Table2 ),
            Table2[Column1] = 6,
            Table2[Column2] = 5,
            Table2[Column3] <> 3
        ) > 0
    ),
    USERELATIONSHIP ( 'Calendar'[Date], Table1[DateUpdated] )
)
return
COUNTROWS( _table )

 

View solution in original post

Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.