cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pawelj795
Post Partisan
Post Partisan

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.

View solution in original post

amitchandak
Super User IV
Super User IV

@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.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.