Create a new table with User_Id and Organization_Type

[Yiyi_1989]

Hi, 

I have two tables which are usage logs and organization info. I tried to create a new table out of these two which include user_id from usage logs and organization type from organization info. 

I used CONCATENATEX to create TypeList for each [user_id] because each [user_id] might be related to several organizations as same user might log into different organizations. 

Then I used SWITCH() to assign the value to the Organization Type that if e.g. the TypeList to certain user_id is "Demo, Product", this user will be a Demo user. 

UserCompanyTypeTable = 
SUMMARIZE (
    'usage table',                                
    'usage table'[user_id],                       
    "Organization Type",
    VAR TypeList =
        CONCATENATEX (
            VALUES('Organization Info'[Type]),   
            'Azure database customer list'[Type],
            ", "
        )
    RETURN
        SWITCH (
            TRUE (),
            TypeList = "Demo, Product", "Demo",
            TypeList = "Product", "Product",
            BLANK ()
        )
)

Unforunately the result I got is blank for my organization type column. Can someone has a look what went wrong? 

[some_bih]

Hi @Yiyi_1989 I think your SUMMARIZE part is not working properly. Please try to use code for New table below firstly, to see that table is created at all. After that switch, or something else is easier part (we exclude your VAR part)

SUMMARIZE (
'Usage Log',
'Usage Log'[user_id],
"User Type",
CONCATENATEX (
RELATEDTABLE('Organization Info'),
[Type],
", ")
)

[some_bih]

Hi @Yiyi_1989 there is no data available so it is hard to spot your issue.

Try to use simple SUMMARIZE part without VAR, so firstly use code below to check is it working at all, later on use your SWITCH logic. 

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

UserCompanyTypeTable =
SUMMARIZE (
'usage table',
'usage table'[user_id],
"Organization Type",
CONCATENATEX (
VALUES('Organization Info'[Type]),
'Azure database customer list'[Type],
", "
)
)

[Yiyi_1989]

Hello @some_bih Thanks so much for your answer. I was not clear in my original question, let me elaborate a bit more here so hopefully it would be clearer to you.  

So as you can see below, I have two tables which are Usage Log (DirectQuery) and Organization Info. The thing is, each user_id in Usage Log may have different OrganizationName in the row context ( because same user might log in to different organization accounts). 

My goal would be create a new list with user_id and also user_type based on the condition of Types in the Organization Info. Below is the code I adjusted but still not working: 

SUMMARIZE (
    'Usage Log',                             
    'Usage Log'[user_id],                       
    "User Type",
     VAR TypeList = CONCATENATEX ( 
             RELATEDTABLE('Organization Info'),  
             [Type],
             ", "),
       
     RETURN
         SWITCH (
            TRUE (),
            CONTAINSSTRING(TypeList,"Internal Demo"), "Internal user",
            CONTAINSSTRING(TypeList,"Company Demo")&& NOT(CONTAINSSTRING(TypeList,"Internal Demo")), "Company Demo User",
            CONTAINSSTRING(TypeList,"Product")&& NOT(CONTAINSSTRING(TypeList,"Internal Demo")), "Company Product User",
            BLANK ()
             )
     )

Just to mention, I used the same logic to create a measure of user_type and it seems correct when I droped it to the visualization with user_id. 

User_type = 

VAR   TypeList = 
CONCATENATEX (
            RELATEDTABLE('Organization Info '),   
            [Type],
            ", ")

RETURN

SWITCH (
            TRUE (),
            CONTAINSSTRING(TypeList,"Internal Demo"), "Internal user",
            CONTAINSSTRING(TypeList,"Company Demo")&& NOT(CONTAINSSTRING(TypeList,"Internal Demo")), "Company Demo User",
            CONTAINSSTRING(TypeList,"Product")&& NOT(CONTAINSSTRING(TypeList,"Internal Demo")), "Company Product User",
            BLANK ()
        )