Converting SQL query to DAX

[Anonymous]

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 

[Greg_Deckler]

@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.

[Anonymous]

@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

[Greg_Deckler]

@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.

[Anonymous]

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