Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |