cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Helper I

## Measure to calculate how many times the same item occured in past weeks

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!

1 ACCEPTED 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

Alberto Ferrari - SQLBI
15 REPLIES 15

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

Alberto Ferrari - SQLBI
Super User

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))`

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

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?

Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

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.

Solution Specialist

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.

Helper I

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 🙂

Solution Specialist

Excellent!

Resolver IV

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.

Super User

You want to use a CALCULATE along with a COUNT and a FILTER clause.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Helper I

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

Helper I

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.

Solution Specialist

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
)
)```
Helper I

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!

Solution Specialist

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.