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

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

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



Anonymous
Not applicable

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

 

 

 

 

MFelix
Super User
Super User

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