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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.