Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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
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.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
11 | |
9 | |
8 | |
8 | |
7 |
User | Count |
---|---|
14 | |
12 | |
11 | |
10 | |
9 |