Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |