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
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
Hi @Cactus26
Here is the calculated field formula i wrote. Here is the workbook.
=COUNTROWS(FILTER(SUMMARIZE(Data,Data[Week],"ABCD",CALCULATE(SUM(Data[Occurrence]),FILTER(data,MAX(Data[Week])-1)),"EFGH",SUM(Data[Occurrence])),[ABCD]>2&&[EFGH]>2))
Hi,
Thank you all for the propositions. Perhaps I was not exact but the thing here is that I need the formula which should look at the items from current week and check the occurence just for them in the past. In other words, how many times the same item from current week was present in the past. If the item from current week was present more than two times in this week or week ago or 2 weeks ago then count it to final figure. Is it possible to do it in DAX?
Hi @Cactus26,
Did you try my solution? If you did and the result is not as expected then let me know of that exact case.
Hi @Ashish_Mathur,
The thing is that I do not store Occurence column in my project. As the occurence depends on filters chosen from slicers. Therefore I cannot create calculated column and I need a measure. Additionally I have thousands of items stored so it will be difficult to use your proposition. I tested @erik_tarnvik solution which is partially good but is missing the condition which check the items just for current week.
Hi again @Cactus26,
glad we are close, did you test my second suggestion, or the one suggested by @AlbertoFerrari (more elegant as I would expect) from him)? My second suggestion does account for selecting a "current week". If this is not what you meant by that, please explain.
Hi @erik_tarnvik,
Yes I tested your two solutions but it did not get fully expected results. However now I tested formula given by @AlbertoFerrari and it works perfectly! Thank you all for the help 🙂
Excellent!
Hi Cactus26,
Try this, I think it should work.
Occurances = VAR CurrentWeek= CALCULATE(MAX(Table1[Week]),ALL(Table1)) RETURN CALCULATE(DISTINCTCOUNT(Table1[Item]), FILTER(Table1, CALCULATE(COUNTROWS(Table1),Table1[Week]>=CurrentWeek-2 && Table1[Week]<=CurrentWeek) >2), Table1[Week]=CurrentWeek)
If you want to use a slicer for the week number you will need to adjust the CurrentWeek variable.
You want to use a CALCULATE along with a COUNT and a FILTER clause.
I tried following but it does not give correct results
=
CALCULATE (
COUNT ( Table[Item] ),
FILTER (
ALLSELECTED ( Table[Week] ),
Table[Week]
>= MAX ( Table[Week] ) - 2
&& Table[Week] <= MAX ( Table[Week] )
)
)
I do not know how to add condition which check the items which are in current week and which were presented in the past weeks more than 2 times
And the Occurence column does not exist in my table because is static and I know that the occurence depends on filters from the report. It was just to present my needs and how I would calculate it when I could do it in Excel.
Hi @Cactus26,
Maybe something like this:
OccuranceCount = VAR CurrentWeek = CALCULATE ( MAX ( 'Table'[Week] ), ALL ( 'Table' ) ) RETURN COUNTROWS ( FILTER ( SUMMARIZE ( FILTER ( 'Table', 'Table'[Week] >= CurrentWeek - 1 ), 'Table'[Item], "NOOC", COUNT ( 'Table'[Item] ) ), [NOOC] > 2 ) )
Hi @erik_tarnvik,
your solution might be good but the thing is that your propositon works just for given data.
But when I would like to apply this on bigger data set and there I have more weeks I choose another week number from filter the measure is not giving any results. Should I apply another type of filter to have it dynamic? Let's say I would like to have one week in one column and in second result from this measure and when I apply some filter i.e country=USA it should re-calculate the measure. I tried play around with this but I am sure I do not understand the filter contexts. Thank you for the help!
Hi @Cactus26,
tried to follow your question, not sure I got it exactly but if you want CurrentWeek to be dynamic, for example from a parameter table, just as Mark was saying you only need to change the definition of CurrentWeek. Say for example that you have a table called Weeks that contain all valid selections for CurrentWeek. Put Weeks[Week] in a slicer and modify as per the below:
OccuranceCount =
VAR CurrentWeek =
IF (
HASONEVALUE ( Weeks[Week] ),
MAX ( Weeks[Week] ),
CALCULATE ( MAX ( Weeks[Week] ), ALL ( Weeks ) )
)
RETURN
COUNTROWS (
FILTER (
SUMMARIZE (
FILTER ( Table1, Table1[Week] >= CurrentWeek - 1 && Table1[Week] <= CurrentWeek),
Table1[Item],
"NOOC", COUNT ( Table1[Item] )
),
[NOOC] > 2
)
)
This should work if you have some other slicer such as Country affecting the context. If I missunderstood your question, please explain and I'll try again.
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 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |