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,
I need to find a way to calculate the measure which will count the number of items which occured more than 2 times in last 2 weeks. We need to check just items from current week. It should be made as measure as the output will depend on filters.
Week | Item | Occurrence |
1 | abc | 2 |
1 | abc | 2 |
1 | cde | 4 |
1 | cde | 4 |
1 | efg | 5 |
1 | uut | 2 |
1 | ooo | 2 |
1 | efg | 5 |
2 | ghi | 3 |
2 | aaa | 1 |
2 | cde | 4 |
2 | cde | 4 |
2 | efg | 5 |
2 | efg | 5 |
2 | ghi | 3 |
2 | lno | 2 |
3 | uut | 2 |
3 | ooo | 2 |
3 | efg | 5 |
3 | ghi | 3 |
3 | lno | 2 |
Occurence column show calculations (Countif). We are in week 3 and items which occured more than 2 times are: efg and ghi. So final measure should show 2. As two items from current week occured more than 2 times in past 2 weeks (including current).
Thank you!
Solved! Go to Solution.
Hi Cactus,
This code looks fine, somewhat tricky, but it does its job.
Occ =
VAR CurrentWeek = CALCULATE ( MAX ( Test[Week] ), ALLSELECTED ( Test[Item] ) )
RETURN
COUNTROWS (
FILTER (
CALCULATETABLE ( VALUES ( Test[Item] ), Test[Week] = CurrentWeek ),
CALCULATE (
COUNTROWS ( Test ),
Test[Week] >= CurrentWeek - 2,
Test[Week] <= CurrentWeek - 1
)
>= 2
)
)
Of course, you might need some minor modification and, most important, understand well how it works, since it hides some complexities in the definitino of CurrentWeek and the determination of the items.
Have fun with DAX!
Alberto Ferrari
http://www.sqlbi.com
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |