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
pawelj795
Post Prodigy
Post Prodigy

Count Items which occurs again

Hi,
I need to create 2 measures.

  • The first one should count how many items occur again today.
  • The second one should tell how many times this particular item occur in the past.

 

Sample data below:

DateItem ID
02.10.2020 (today)

A

02.10.2020B
02.10.2020C
01.10.2020B
28.09.2020C
27.09.2020C
1 ACCEPTED SOLUTION
v-xuding-msft
Community Support
Community Support

Hello @pawelj795 ,

Please try this:

Measure 1 = 
var t1_= 
    CALCULATETABLE (
        VALUES ( 'Table'[Item ID] ),
        FILTER (
            ALL ( 'Table'[Date] ),
            'Table'[Date] < MAX('Table'[Date]) )
        )
var t2_= 
    CALCULATETABLE (
        VALUES ( 'Table'[Item ID] ),
        FILTER (
            ALL ( 'Table'[Date] ),
            'Table'[Date] = MAX('Table'[Date]) )
        )
return
COUNTROWS(INTERSECT(t1_,t2_))
Measure 2 = CALCULATE(COUNT('Table'[Date]),ALLEXCEPT('Table','Table'[Item ID]))
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-xuding-msft
Community Support
Community Support

Hello @pawelj795 ,

Please try this:

Measure 1 = 
var t1_= 
    CALCULATETABLE (
        VALUES ( 'Table'[Item ID] ),
        FILTER (
            ALL ( 'Table'[Date] ),
            'Table'[Date] < MAX('Table'[Date]) )
        )
var t2_= 
    CALCULATETABLE (
        VALUES ( 'Table'[Item ID] ),
        FILTER (
            ALL ( 'Table'[Date] ),
            'Table'[Date] = MAX('Table'[Date]) )
        )
return
COUNTROWS(INTERSECT(t1_,t2_))
Measure 2 = CALCULATE(COUNT('Table'[Date]),ALLEXCEPT('Table','Table'[Item ID]))
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
amitchandak
Super User
Super User

@pawelj795 ,

The information you have provided is not making the problem clear to me. Can you please explain with an example.

These can be two measures what I can get from a high level

 

calculate(distinctcount(Table[Item ID]), Table[Date] =today())

calculate(distinctcount(Table[Item ID]), Table[Date] < today())


Appreciate your Kudos.

Hi @amitchandak 

Alluding to my above sample, I explain what I meant.

 

  • First measure:
    The result should be 2, because only item "B" and item "C" appear again today.
  • Second measure:
    For the Item "B" the result should be 2. (also include today) - It appeared on 02.10.2020 and 01.10.2020
    For the Item "C" the result should be 3. - It appeared on 02.10.2020, 28.09.2020, and 27.09.2020.

    If it's still not clear to you, please tell me which part is confusing.

.

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.