How to create a dynamic table? I got the error that column was not found in the input table :(

[Yiyi]

Hello experts!

I have a table of Usage Logs and another table of Organization Info.  I want to create a new dynamic table combining user_id and organization and also organization type. Because some user_id can be logged into different organizations, I need a table which will assign only one organization and organizational type to the user. Please see my table example as following: 

The usage logs is like the following: 

User_Id	Organization	Event
111	A	Page Views
111	B	Page Views
113	C	...

The Organization Info is like the following: 

Organization	Organization Type
A	Internal company
B	External company
C	External company

The result I want to get is the following: 

(The logic is if a same user_id is logged in Organization A and Organization B, it will only count as a a user for the internal company and not external company.)

User_Id	Organization	Organization Type
111	A	Internal company
113	C	External company

Could some one help me to create a dynamic table? I tried the following but it doesn't work . The error message said the column 'Organization Type' specified in the 'Summazize' function was not found in the input table. Thanks a lot and I appreciate your help! 

DynamicTable = 
VAR UserTypes =
        SUMMARIZE('usage logs', 'usage logs'[user_Id], 'organization info'[Organization Type])
RETURN
    SUMMARIZE(
        UserTypes,
        'usage logs'[user_Id],
        "Organization Type",
        SWITCH(
            TRUE(),
            CONTAINSSTRING(UserTypes, "Internal company") && CONTAINSSTRING(UserTypes, "External company"), "Internal company"
            BLANK()
        )
    )

[scee07]

Hi, 

here is my approach. As always, based on my current understanding of your problem. 

I have set up a relationship between UserLogs and Organization in both directions. If this should not be permanent in your model you can activate it in DAX for your measure (USERRELATIONSHIP)
Here is my organization type measure:

MyType =
VAR thisOrg =
    SELECTEDVALUE ( UserLogs[Organization] )
VAR thisType =
    CALCULATE (
        FIRSTNONBLANK ( Organization[OrganizationType], 0 ),
        FILTER ( ALL ( Organization ), Organization[OrganizationID] = thisOrg )
    )
VAR standardType =
    CALCULATE (
        FIRSTNONBLANK ( Organization[OrganizationType], 0 ),
        FILTER (
            ALL ( Organization ),
            CONTAINSSTRING ( Organization[OrganizationType], "Internal" )
        )
    )
RETURN
    IF (
        COUNTROWS ( Organization ) > 1
            && CONTAINSSTRING ( thisType, "External" ),
        BLANK (),
        IF ( COUNTROWS ( Organization ) = 1, thisType, standardType )
    )

The idea is that the relationship filters already the organizations for the user because, the user id in the visual table column will set the filter context to this user. 

With my understanding of the business rule, if more than one rows are filtered we take the standard organisation of the user, otherwise wie take the one in the filter context. With the exception that if more than 1 row is filtered and the current context is external company, then blank() will be returned. Then the row is not shown in the table visual. 

The similar measure for the org:

MyOrg =
VAR thisOrg =
    SELECTEDVALUE ( UserLogs[Organization] )
VAR thisType =
    CALCULATE (
        FIRSTNONBLANK ( Organization[OrganizationType], 0 ),
        FILTER ( ALL ( Organization ), Organization[OrganizationID] = thisOrg )
    )
VAR standardOrg =
    CALCULATE (
        FIRSTNONBLANK ( Organization[OrganizationID], 0 ),
        FILTER (
            ALL ( Organization ),
            CONTAINSSTRING ( Organization[OrganizationType], "Internal" )
        )
    )
RETURN
    IF (
        COUNTROWS ( Organization ) > 1
            && CONTAINSSTRING ( thisType, "External" ),
        BLANK (),
        IF ( COUNTROWS ( Organization ) = 1, thisOrg, standardOrg )
    )

Hope this helps. 
Best regards 
Christian