Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Hi @Anonymous ,
Do the following:
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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @Anonymous ,
Do the following:
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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português@MFelix wow...it look pretty dificult...Ill try to figure out what you did...I appreciate all your help!!!!
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHello @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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |