Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Distinct Count on ID

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

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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] )
    )
)

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      Anonymous Thank you for this!

  • themistoklis's avatar
    themistoklis
    Community Champion
    7 years ago

    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