cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

I need to count only repetead values (ID numbers) dinamically according to a date filter.

IS IT POSSIBLE OR ROCKET ENGINEERING?????????????????????

 

I need to count only repetead values (ID numbers) dinamically according to a date filter.

 

Im not even close to a solution...is there any big brain available to help me out?

 

Examples of 2 desired results.

 

 

Question NASA.JPG

 

ID NUMBER	Discount	Star date
1000	15%	01/01/2020
2000	15%	01/01/2020
3000	15%	01/01/2020
3001	15%	01/01/2020
3002	15%	01/01/2020
3003	15%	01/01/2020
3007	15%	01/01/2020
3011	20%	01/02/2020
2000	20%	01/02/2020
3500	20%	01/02/2020
3501	20%	01/02/2020
1000	15%	01/02/2020
3502	20%	01/02/2020
3503	20%	01/02/2020
3504	30%	01/03/2020
3510	30%	01/03/2020
2000	30%	01/04/2020
3700	30%	01/04/2020
3701	30%	01/04/2020
3702	10%	01/04/2020
3703	10%	01/04/2020
3007	10%	01/04/2020
3706	10%	01/05/2020
3707	10%	01/05/2020
2000	10%	01/05/2020
4000	10%	01/05/2020
4001	10%	01/05/2020
1000	10%	01/06/2020
3007	10%	01/06/2020
5001	50%	01/06/2020
3700	30%	01/06/2020
5003	50%	01/06/2020
3007	50%	01/06/2020

 

1 ACCEPTED SOLUTION
Super User III
Super User III

Hi @Anonymous ,

 

Do the following:

  • Create two disconected tables with unique values:
    • Discounts
    • Users
  • Add the following measures to your model:
Discount Count =
VAR temptable =
    FILTER (
        SUMMARIZE (
            ALLSELECTED ( 'Table (2)' );
            'Table (2)'[ID NUMBER];
            "@Count_User"; COUNT ( 'Table (2)'[ID NUMBER] )
        );
        [@Count_User] > 1
    )
VAR User_Columns =
    SELECTCOLUMNS ( temptable; "User_ID"; 'Table (2)'[ID NUMBER] )
VAR Discount_Selected =
    SELECTEDVALUE ( 'Discounts'[Discount] )
VAR countvalues =
    CALCULATE (
        COUNT ( 'Table (2)'[ID NUMBER] );
        'Table (2)'[Discount] = Discount_Selected;
        'Table (2)'[ID NUMBER] IN User_Columns
    )
RETURN
    countvalues + 0

Discounts Total Count = SUMX('Discounts';[Discount Count])


User Count =
VAR temptable =
    FILTER (
        SUMMARIZE (
            ALLSELECTED ( 'Table (2)' );
            'Table (2)'[ID NUMBER];
            "@Count_User"; COUNT ( 'Table (2)'[ID NUMBER] )
        );
        [@Count_User] > 1
    )
VAR User_Selected =
    SELECTEDVALUE ( Users[ID NUMBER] )
RETURN
    CALCULATE (
        COUNT ( 'Table (2)'[ID NUMBER] );
        'Table (2)'[ID NUMBER] = User_Selected
    ) + 0


User Total Count=
VAR temptable =
    FILTER (
        SUMMARIZE (
            ALLSELECTED ( 'Table (2)' );
            'Table (2)'[ID NUMBER];
            "@ID_Count"; COUNT ( 'Table (2)'[ID NUMBER] )
        );
        [@ID_Count] > 1
    )
VAR USER_SELECTION =
    SELECTCOLUMNS ( temptable; "User_ID"; 'Table (2)'[ID NUMBER] )
RETURN
    IF (
        HASONEFILTER ( USers[ID NUMBER] );
        IF ( SUMX ( USers; [User Count] ) > 1; "Yes" );
        COUNTROWS ( USER_SELECTION )
    )

 

Now just us the Discounts Total Count, User Total Count and the dimension table to setup your model.

 

MFelix_0-1603902682291.pngMFelix_1-1603902697896.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

4 REPLIES 4
Super User III
Super User III

Hi @Anonymous ,

 

Do the following:

  • Create two disconected tables with unique values:
    • Discounts
    • Users
  • Add the following measures to your model:
Discount Count =
VAR temptable =
    FILTER (
        SUMMARIZE (
            ALLSELECTED ( 'Table (2)' );
            'Table (2)'[ID NUMBER];
            "@Count_User"; COUNT ( 'Table (2)'[ID NUMBER] )
        );
        [@Count_User] > 1
    )
VAR User_Columns =
    SELECTCOLUMNS ( temptable; "User_ID"; 'Table (2)'[ID NUMBER] )
VAR Discount_Selected =
    SELECTEDVALUE ( 'Discounts'[Discount] )
VAR countvalues =
    CALCULATE (
        COUNT ( 'Table (2)'[ID NUMBER] );
        'Table (2)'[Discount] = Discount_Selected;
        'Table (2)'[ID NUMBER] IN User_Columns
    )
RETURN
    countvalues + 0

Discounts Total Count = SUMX('Discounts';[Discount Count])


User Count =
VAR temptable =
    FILTER (
        SUMMARIZE (
            ALLSELECTED ( 'Table (2)' );
            'Table (2)'[ID NUMBER];
            "@Count_User"; COUNT ( 'Table (2)'[ID NUMBER] )
        );
        [@Count_User] > 1
    )
VAR User_Selected =
    SELECTEDVALUE ( Users[ID NUMBER] )
RETURN
    CALCULATE (
        COUNT ( 'Table (2)'[ID NUMBER] );
        'Table (2)'[ID NUMBER] = User_Selected
    ) + 0


User Total Count=
VAR temptable =
    FILTER (
        SUMMARIZE (
            ALLSELECTED ( 'Table (2)' );
            'Table (2)'[ID NUMBER];
            "@ID_Count"; COUNT ( 'Table (2)'[ID NUMBER] )
        );
        [@ID_Count] > 1
    )
VAR USER_SELECTION =
    SELECTCOLUMNS ( temptable; "User_ID"; 'Table (2)'[ID NUMBER] )
RETURN
    IF (
        HASONEFILTER ( USers[ID NUMBER] );
        IF ( SUMX ( USers; [User Count] ) > 1; "Yes" );
        COUNTROWS ( USER_SELECTION )
    )

 

Now just us the Discounts Total Count, User Total Count and the dimension table to setup your model.

 

MFelix_0-1603902682291.pngMFelix_1-1603902697896.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

Anonymous
Not applicable

@MFelix  wow...it look pretty dificult...Ill try to figure out what you did...I appreciate all your help!!!!

 

 

 

 

Super User III
Super User III

Hi @Anonymous ,

 

Something is not correct on your data or I'm not getting the information correctly.

 

If I look at the first image where are you picking up 4 repetead values with 10 % I'm only getting 1 value  that is the 3007 in the June and April months all other values are unique, for the 15% I'm only picking up  the ID 1000 and for 0.3 I'0m only picking up 3700.

 

can you explain a little bit better the logic behind your calculation

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Anonymous
Not applicable

Hello @MFelix I thoght no one was gonna help me!

 

Ok let me explain better (Im so sorry):

 

First thing I need is to count repeated ID numbers and identify them somehow....  in the period selected In the example we have (1000=repeated 3 times; 2000 =repeated 4 times; 3007=repeated 3 times and 3700=repeated 2 times)... I need to ignore unique id numbers in the period selected.

 

Second thing, without considering ID numbers, I need to know which discounts were associated with repeated numbers example:

 

Repeated ID numbers which have 10% associated were 3007;2000;1000;3007 (four ID numbers) and repeated numbers which have 20% associated was only one 2000 (1 Id number) and so one with the rest with the rest of ID Numbers... so the answer I want to respond is how many times 10%, 20%, 15%,30%... descounts were associated to a repeated numbers in the period selected.

 

I dont know why it sounds easy to do it but I cant.... is this really posible?

 

Tel me if you have an idea. or maybe a no that profesional solution.

 

 

 

 

 

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

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.

Top Solution Authors
Top Kudoed Authors