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.
Hi All,
I have a scenario. I need to get repeated values in a measure based on some date constraint.
Example:
Ids' may repeat in year.
I need a distinct count of Ids which are present in the first half as well as in the second half if I add date constraint as 6 months.
In Slicer start date 1/06/2011 to 1/12/2011
Id Date Amount Status(Measure)
1000 1/1/2011 25.00 Inactive
2000 2/2/2011 500.00 Inactive
1000 1/08/2011 89.00 active
2000 01/09/2011 45.00 Active
Now I require the count of id. The output should be 2.
Solved! Go to Solution.
Hi , @manideep547
Please try the measure as below :
count of id =
COUNTROWS (
FILTER (
SUMMARIZE (
'Table',
'Table'[Id],
"InDate", CALCULATE (
SUM ( 'Table'[Amount ] ),
'Table'[Date] IN FILTERS ( 'Date'[Date] )
),
"OutDate", CALCULATE (
SUM ( 'Table'[Amount ] ),
NOT 'Table'[Date] IN FILTERS ( 'Date'[Date] )
)
),
[InDate] <> [OutDate]
)
)
And it shows as below :
Here is a sample :
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi , @manideep547
Please try the measure as below :
count of id =
COUNTROWS (
FILTER (
SUMMARIZE (
'Table',
'Table'[Id],
"InDate", CALCULATE (
SUM ( 'Table'[Amount ] ),
'Table'[Date] IN FILTERS ( 'Date'[Date] )
),
"OutDate", CALCULATE (
SUM ( 'Table'[Amount ] ),
NOT 'Table'[Date] IN FILTERS ( 'Date'[Date] )
)
),
[InDate] <> [OutDate]
)
)
And it shows as below :
Here is a sample :
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
113 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |