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

[Anonymous]

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.

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

[MFelix · Accepted solution]

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.

[Anonymous]

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

[MFelix]

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

[Anonymous]

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.