Converting SQL query to DAX

Anonymous — Mon, 09 Aug 2021 17:43:09 GMT

Scenario ::: I want to convert the SQL below to DAX , as you can see in SQL I am using ROW_Number windows function to partition by ssn and eomdate…to get the CostCenter associated with a SSN based on the month selection …. As the table shows below CostCenterID can change for a SSN from one month to the other . For example SSN=2836 had costcenterID=9 by end of march but for all previous months the Costcenterid was 23. So for me to correctly calculate the Total Members for a CostCenter for a given date range.......

The input data looks like this::::

SSN	CostCenterID	EOMDate
44694	9	3/31/2021
44694	9	2/28/2021
44694	9	1/31/2021
28369	9	3/31/2021
28369	23	2/28/2021
28369	23	1/31/2021
33423	15	3/31/2021
33423	15	2/28/2021
33423	15	1/31/2021
13423	15	3/31/2021
13423	15	2/28/2021
13423	15	1/31/2021
18456	23	2/28/2021
18456	23	1/31/2021
12452	23	3/31/2020
12452	23	2/28/2021
12452	23	1/31/2021
46572	15	3/31/2021
46572	20	2/28/2021
46572	20	1/31/2021
46571	20	3/31/2021
46571	20	2/28/2021
46571	20	1/31/2021
46533	20	2/28/2021
46533	20	1/31/2021
56811	20	3/31/2021
56811	23	2/28/2021
56811	23	1/31/2021

Here is the SQL query I want converted to DAX based on date selected by the user via the date slicer in the PowerBI report

Select z. SSN
, z.CostCenterID
INTO #T_CostCenterLatest
From (
SELECT
SSN
, CostCenterID
, RowNumber = ROW_NUMBER() OVER (PARTITION BY SSN ORDER BY EOMDate DESC)
FROM [TransArchive].dbo.AGGR_Transaction_ChannelUtilization
WHERE EOMDate BETWEEN @dteEOMBegin AND @dteEOMEnd
)z
Where z.RowNumber=1 --Cost center of most recent record in dates selected

--Get results for the selected date
SELECT
CostCenterID=t.CostCenterID
, t.EOMDate
, TotalMembers=COUNT(DISTINCT(t.SSN))
FROM [TransArchive].dbo.AGGR_Transaction_ChannelUtilization t
INNER JOIN #T_CostCenterLatest tc ON t.SSN = tc.SSN
LEFT JOIN dbo.COST_CENTER c ON tc.CostCenterID = c.COST_CENTER_ID
GROUP BY tc.CostCenterID,c.COST_CENTER_NBR, t.EOMDate
order by EOMDate,CostCenterID

Expected result ::: Here both the Date Range and CostCenterID values will be PowerBI report driven . I need to get the DAX for Total Members....Here I am showing the expected results based on the two Date Ranges the user selects and how that affects the Total Members count...

CostcenterID	Date Range	Total Members
9	1/1/2021 - 3/31/2021	2
15	1/1/2021 - 3/31/2021	3
20	1/1/2021 - 3/31/2021	2
23	1/1/2021 - 3/31/2021	1

CostcenterID	Date Range	Total Members
9	1/1/2021 - 2/28/2021	1
15	1/1/2021 - 2/28/2022	2
20	1/1/2021 - 2/28/2023	3
23	1/1/2021 - 2/28/2024	4

Hopefully this makes sense.

Thanks in advance

Re: Converting SQL query to DAX

lbendlin — Tue, 10 Aug 2021 19:35:31 GMT

You don't really need DAX for that. Take your data as is, and put it into a matrix visual. Set the value aggregation to Count Distinct of SSN. Done.

topic Converting SQL query to DAX in DAX Commands and Tips

Converting SQL query to DAX

Re: Converting SQL query to DAX