Forum Discussion

tangcy12345's avatar
tangcy12345
New Member
3 years ago

How to get data for valid users

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: 

DEALERUSERS_IDPARTNUMQUANTITYTIMESTAMP
CE22045834100112022/9/6 19:37
CE22166889100212022/9/6 14:49
CE22162242100312022/9/6 10:46
CE22166416100412022/9/4 13:24
CE22112288100512022/9/3 7:54
CE21577772100632022/9/1 17:38
CE21577772100732022/9/1 17:38
CE21577772100832022/9/1 17:38
EC21501741100912022/9/7 11:04
EC21501741101022022/9/7 11:04
EC221681221011102022/9/7 11:04
EC22168122101212022/9/7 11:03
EC22168122101312022/9/7 11:03
EC22168122101412022/9/7 11:03
EC22168122101512022/9/7 11:03
EC22168122101612022/9/7 11:02
EC21595086101712022/9/7 11:00
EC21595086101812022/9/7 11:00
EC21595086101912022/9/7 11:00
EC21595086102012022/9/7 11:00
LS21533324102112022/9/7 11:04
LS21522110102212022/9/7 10:46
LS21522110102312022/9/7 10:46
LS21496009102412022/9/7 10:05
LS21523067102512022/9/7 9:48
LS21523067102612022/9/7 9:48
LS21523067102712022/9/7 9:48
NC213408751028502022/9/6 14:21
NC22167606102912022/9/6 10:41
NC21471431103012022/9/5 16:52
NC21471431103122022/9/5 16:52
NC21471431103222022/9/5 16:52
NC21471431103312022/9/5 16:52
NC21471431103422022/9/5 16:52
NC21471431103512022/9/5 16:37
NC21471431103632022/9/5 16:37
NC21471431103712022/9/5 16:37
NC21471431103712022/9/5 16:37
NC21471431103812022/9/5 16:37

 

ObsleteUsers

User_id
21577772
21501741
21533324
21340875
Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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