How to get data for valid users

[tangcy12345]

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?

VALID_USERS_ID =

VAR A =  

 CALCULATETABLE(

    VALUES('Shoping_Cart'[USERS_ID] ),  //all users

    FILTER(

         ALL('Shoping_Cart'),

        'Shoping_Cart'[USERS_ID] <> BLANK()

    ))

VAR B =  

 CALCULATETABLE(

    VALUES('ObsleteUsers'[User_id]), // obslete users

    FILTER(

         ALL('ObsleteUsers'),

        'ObsleteUsers'[User_id] <> BLANK()

    ))

VAR C =EXCEPT(A,B) // valid users

RETURN C

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

[Anonymous]

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