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.
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
Solved! Go to 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] ) ) )
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 Thank you for this!
@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
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 |
---|---|
105 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |