what measure can I create to check if Client is active between the dateslicer group

[mak]

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

[Phil_Seamark]

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

[mak]

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?

[mak]

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

[Sean]

@mak

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

[Sean]

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!

[mak]

Thank you Sean, how to ignore the values which will be NULL

[Sean · Accepted solution]

Mark is not included

[mak]

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

[mak]

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