The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I have a Direct Query Table
Client -which has Start Date and End Date
Also have a Slicer Table, where i have created a hierarchy of dates and name it as 'SLICER_HIERARCHY'
Question
Tried many times, but not getting any syntax, to create a measure that will give me All active clients who fall in the dateRange based on the Slicer selected
thank you so much
Solved! Go to Solution.
Hi @mak,
Do you have a small sample of the table in question so we can have a crack at your measure for you.
Cheers,
Phil
Thank you Phil, belows is my table with data
UserTable Table
UserId | StartDate | EndDate | Name
1 null null Mark
2. 2017-03-07 00:00:00.000 2018-03-08 00:00:00.000 Rick
3. 2017-02-05 00:00:00.000 2018-01-08 00:00:00.000 John
4. 2017-03-06 00:00:00.000 2018-03-09 00:00:00.000 Martin
Dates Table
SlicerDate
2017-02-01 00:00:00.000
2017-02-02 00:00:00.000
-----
2017-0-08 00:00:00.000
2017-03-08 00:00:00.000
2017-03-01 00:00:00.000
2017-03-02 00:00:00.000
2017-03-07 00:00:00.000
I uses GroupBy to create a Hierarchy in Table
How do i find all the Active Users based on the Slicer, there can be null values?
This is what I am looking for, not not sure how to put it in dax
AllActiveClients = COUNTROWS('Client', FILTER('Client',
'Slicer.SlicerHierarchy' > 'Client.startDate' && 'Slicer.SlicerHierarchy'<'Client.endDate']
Something like above should give me all the rows
OR
Should I go row by row and leave the SUM to the controls when i add it to the UI
Here's your general Active Count Measure
Remember no relationship between 'Table' and 'Calendar Table'
Active Count = CALCULATE ( DISTINCTCOUNT ( Table[ORDER_ID] ), FILTER ( Table, Table[START_DATE] <= LASTDATE ( CalendarTable[Date] ) && Table[END_DATE] >= FIRSTDATE ( CalendarTable[Date] ) ) )
Look at my response here
http://community.powerbi.com/t5/Desktop/Count-Number-Of-Active-Orders/m-p/110285#M46441
Here's the formula for your sample
Active Count = CALCULATE ( DISTINCTCOUNT ( 'Table'[UserID] ), FILTER ( 'Table', 'Table'[StartDate] <= LASTDATE ( CalendarTable[Date] ) && 'Table'[EndDate] >= FIRSTDATE ( CalendarTable[Date] ) ) )
and the result...
Hope this helps!
Thank you Sean, how to ignore the values which will be NULL
Mark is not included
Thanks Sean,
There is one small issue, I have used Group by feature to create a Hierarychy column which does not appear as a selection in Measure, for each of the selection..
So Do I need to create four different measures for each type, if so how will I use it for a drill down chart
attached is the image for same
thanks
Thank you Sean, that solved the problem
Instead of DAX query based on based on Hierarchy i got the Measure based on the root date of which Hierarchy is Based and it fixed the issue
Thank you again
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
96 | |
80 | |
62 | |
56 |
User | Count |
---|---|
256 | |
120 | |
112 | |
83 | |
71 |