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.
Hi,
I have 2 tables, one is shopping cart with all user, another is obslete users.
I created a measure name : VALID_USERS_ID, it has remved obslete users from all users.
I want to create a visual report with VALID_USERS_ID,DEALER, PARTNUM
My Question: get error as bleow screen, is there any good solution?
Shopping_cart:
DEALER | USERS_ID | PARTNUM | QUANTITY | TIMESTAMP |
CE | 22045834 | 1001 | 1 | 2022/9/6 19:37 |
CE | 22166889 | 1002 | 1 | 2022/9/6 14:49 |
CE | 22162242 | 1003 | 1 | 2022/9/6 10:46 |
CE | 22166416 | 1004 | 1 | 2022/9/4 13:24 |
CE | 22112288 | 1005 | 1 | 2022/9/3 7:54 |
CE | 21577772 | 1006 | 3 | 2022/9/1 17:38 |
CE | 21577772 | 1007 | 3 | 2022/9/1 17:38 |
CE | 21577772 | 1008 | 3 | 2022/9/1 17:38 |
EC | 21501741 | 1009 | 1 | 2022/9/7 11:04 |
EC | 21501741 | 1010 | 2 | 2022/9/7 11:04 |
EC | 22168122 | 1011 | 10 | 2022/9/7 11:04 |
EC | 22168122 | 1012 | 1 | 2022/9/7 11:03 |
EC | 22168122 | 1013 | 1 | 2022/9/7 11:03 |
EC | 22168122 | 1014 | 1 | 2022/9/7 11:03 |
EC | 22168122 | 1015 | 1 | 2022/9/7 11:03 |
EC | 22168122 | 1016 | 1 | 2022/9/7 11:02 |
EC | 21595086 | 1017 | 1 | 2022/9/7 11:00 |
EC | 21595086 | 1018 | 1 | 2022/9/7 11:00 |
EC | 21595086 | 1019 | 1 | 2022/9/7 11:00 |
EC | 21595086 | 1020 | 1 | 2022/9/7 11:00 |
LS | 21533324 | 1021 | 1 | 2022/9/7 11:04 |
LS | 21522110 | 1022 | 1 | 2022/9/7 10:46 |
LS | 21522110 | 1023 | 1 | 2022/9/7 10:46 |
LS | 21496009 | 1024 | 1 | 2022/9/7 10:05 |
LS | 21523067 | 1025 | 1 | 2022/9/7 9:48 |
LS | 21523067 | 1026 | 1 | 2022/9/7 9:48 |
LS | 21523067 | 1027 | 1 | 2022/9/7 9:48 |
NC | 21340875 | 1028 | 50 | 2022/9/6 14:21 |
NC | 22167606 | 1029 | 1 | 2022/9/6 10:41 |
NC | 21471431 | 1030 | 1 | 2022/9/5 16:52 |
NC | 21471431 | 1031 | 2 | 2022/9/5 16:52 |
NC | 21471431 | 1032 | 2 | 2022/9/5 16:52 |
NC | 21471431 | 1033 | 1 | 2022/9/5 16:52 |
NC | 21471431 | 1034 | 2 | 2022/9/5 16:52 |
NC | 21471431 | 1035 | 1 | 2022/9/5 16:37 |
NC | 21471431 | 1036 | 3 | 2022/9/5 16:37 |
NC | 21471431 | 1037 | 1 | 2022/9/5 16:37 |
NC | 21471431 | 1037 | 1 | 2022/9/5 16:37 |
NC | 21471431 | 1038 | 1 | 2022/9/5 16:37 |
ObsleteUsers
User_id |
21577772 |
21501741 |
21533324 |
21340875 |
Hi @tangcy12345 ,
From your screenshot not too sure if you are trying to get the count of users. If so please use,
VALID_USERS_ID =
VAR A =
CALCULATE (
DISTINCTCOUNT ( 'Shoping_Cart'[USERS_ID] ),
//all users
FILTER (
ALL ( 'Shoping_Cart' ),
'Shoping_Cart'[USERS_ID] <> BLANK ()
)
)
VAR B =
CALCULATE (
DISTINCTCOUNT ( 'ObsleteUsers'[User_id] ),
// obslete users
FILTER (
ALL ( 'ObsleteUsers' ),
'ObsleteUsers'[User_id] <> BLANK ()
)
)
VAR C =
EXCEPT ( A, B ) // valid users
RETURN
C
BR
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 | |
98 | |
78 | |
64 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |