Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Anonymous
Not applicable

Converting SQL query to DAX

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

1 REPLY 1
lbendlin
Super User
Super User

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.

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.