Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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 )
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.
Pat
To learn more about Power BI, follow me on Twitter or subscribe 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 )
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |