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.
We have a table which includes 2 collums with dates, collum with ID and collum with owner. We need to count how many unique records are in the table on each owner, which considers our condition.
Example of the table:
Date1 | Owner | ID | Date2 |
16.06.2021 | Vika | 1712 | 30.06.2021 |
25.02.2021 | Vika | 1513 | 30.06.2021 |
25.02.2021 | Vika | 1608 | 30.06.2021 |
25.05.2021 | Vika | 1632 | 29.06.2021 |
25.05.2021 | Vika | 1648 | 30.06.2021 |
15.04.2021 | Taras | 1647 | 18.06.2021 |
31.05.2021 | Stepan | 1655 | 01.06.2021 |
16.06.2021 | Julia | 1213 | 25.06.2021 |
15.04.2021 | Julia | 1412 | 16.07.2021 |
25.03.2021 | Julia | 1515 | 30.07.2021 |
21.05.2021 | Julia | 1640 | 25.06.2021 |
The condition is:
date 1 < min calendar date
Date 2 >= min calendar date
Date 2<= max calemdar date
So for example. if we will put calendar dates like 01.06.2021-30.06.2021.
We need to count how many records meet our conditions. So in result we must receive:
Vika - 4
Taras - 1
Stepan - 1
Julia - 1
Please help us to solve this task. Thank you in advance!
Solved! Go to Solution.
Hi @DimaMD
Try this one:
Count =
VAR _Min =
MIN ( 'Date'[Date] )
VAR _Max =
MAX ( 'Date'[Date] )
RETURN
CALCULATE (
COUNTROWS ( 'Table' ),
ALLEXCEPT ( 'Table', 'Table'[Owner] ),
'Table'[Date1] < _Min
&& 'Table'[Date2] >= _Min
&& 'Table'[Date2] <= _Max
)
Output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Hi @DimaMD
Try this measure:
Count =
Var _Min=MIN('Date'[Date])
Var _Max=MAX('Date'[Date])
return
CALCULATE(
COUNTROWS('Table'),
'Table'[Date1] < _Min,
'Table'[Date2] >= _Min&&'Table'[Date2] <= _Max
)
Output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Sorry @VahidDM , but this measure does not work, result is blank😥
I can't understand why your measure worked
Relationship between table and calendar Date1
Hi @DimaMD
Try this one:
Count =
VAR _Min =
MIN ( 'Date'[Date] )
VAR _Max =
MAX ( 'Date'[Date] )
RETURN
CALCULATE (
COUNTROWS ( 'Table' ),
ALLEXCEPT ( 'Table', 'Table'[Owner] ),
'Table'[Date1] < _Min
&& 'Table'[Date2] >= _Min
&& 'Table'[Date2] <= _Max
)
Output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Hi, @VahidDM
Thank you, the measure worked
Hi,
This measure gets your example result.
Note, it uses DAX syntax introduced in September 2021 Desktop, so you'll need to be using that version.
Count =
CALCULATE(
COUNTROWS('Table'),
'Table'[Date1] < MIN('Date'[Date]),
'Table'[Date2] >= MIN('Date'[Date]),
'Table'[Date2] <= MAX('Date'[Date])
)
Sorry, but this measure does not work, result is blank
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 |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |