Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Cactus26
Helper I
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.

 

WeekItemOccurrence
1abc2
1abc2
1cde4
1cde4
1efg5
1uut2
1ooo2
1efg5
2ghi3
2aaa1
2cde4
2cde4
2efg5
2efg5
2ghi3
2lno2
3uut2
3ooo2
3efg5
3ghi3
3lno2

 

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

View solution in original post

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
Ashish_Mathur
Super User
Super User

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

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

 

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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!

MarkS
Resolver IV
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.

Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler,

 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.