topic Re: Converting SQL query to DAX in DAX Commands and Tips

Converting SQL query to DAX

Anonymous — Sat, 07 Aug 2021 01:45:46 GMT

Let me try this one more time...

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 now

Thanks in advance

Greg_Deckler

Re: Converting SQL query to DAX

Greg_Deckler — Tue, 03 Aug 2021 23:57:05 GMT

Anonymous Hard to make heads or tails of the SQL query, you might want to instead list sample data and expected output. But, seems like a Complex Selector of the form:

Measure = VAR __Min = MIN('ReportDate'[Date]) VAR __Max = MAX('ReportDate'[Date]) RETURN IF(MAX('AGGR_Transaction_ChannelUtilization'[EOMDate])>= __Min && MAX('AGGR_Transaction_ChannelUtilization'[EOMDate])<= __Max,1,0)

Otherwise, Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Re: Converting SQL query to DAX

Anonymous — Thu, 05 Aug 2021 05:29:50 GMT

Greg_Deckler Thanks for your prompt reply

Please see the original post with the full query. The Date range given in the query is just an example , but the in actual PowerBI report user will use the slicer as shown above and based on that my DAX has to calculate the total members for a costcenter. Basically I dont want to count members twice within a given date range that user selects .

Please let me know if this is ok and/or if you need anything else.

Thanks again

Re: Converting SQL query to DAX

Greg_Deckler — Thu, 05 Aug 2021 12:20:59 GMT

Anonymous Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Re: Converting SQL query to DAX

Anonymous — Mon, 09 Aug 2021 05:30:19 GMT

I have taken another shot at trying and explaining the scenario. Hopefully this makes sense now . Please see the original post for details

Thanks

Greg_Deckler