Distinct Count on ID

[Anonymous]

Helllo

I hope you can help?

I have two tables

Date Table

Start Date/Time                  End Date/Time

01/01/2018 11:00               01/01/2018 12:00

01/01/2018 12:00               01/01/2018 13:00

01/01/2018 13:00               01/01/2018 14:00

01/01/2018 14:00               01/01/2018 15:00

FACT Table with double entries for the UUID

UUID                                                       Start Date                          USER ID

hjjwehfhgwfbvwdlwkvhn                        01/01/2018 11:00             4444

hjjwehfhgwfbvwdlwkvhn                        01/01/2018 11:00             3333

jwhfdewgvjhvwbvbjwsvjb                       01/01/2018 11:30             1111

jwhfdewgvjhvwbvbjwsvjb                       01/01/2018 11:30             2222

I would like to add a column or create a measure on the Date Table that shows me how many times a Start Date in the FACT Table appears between the Start Date/Time and End Date/Time on the Date Table, per UUID

The Result would look like this

Start Date/Time                  End Date/Time                    Value

01/01/2018 11:00               01/01/2018 12:00                  2

01/01/2018 12:00               01/01/2018 13:00                  0

Thanks in advance

Joe

[Anonymous · Accepted solution]

You can do it like this:

=
CALCULATE (
    DISTINCTCOUNT ( FactTable[UUID] );
    FILTER (
        FactTable;
        FactTable[Start Date] >= MIN ( DateTable[Start Date/Time] )
            && FactTable[Start Date] <= MAX ( DateTable[End Date/Time] )
    )
)

[Anonymous]

@Anonymous Thank you for this!

[themistoklis]

@Anonymous

Create a measure which counts the dates in the fact table like this:

Measure = DISTINCTCOUNT('Fast Table'[Start Date])

Then add a table object with dimension the date from the Date Table and measure the field above