Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
Yiyi
Helper I
Helper I

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

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 
111APage Views 
111BPage Views 
113C... 

 

The Organization Info is like the following: 

 

Organization Organization Type 
AInternal company 
BExternal company 
CExternal 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 
111AInternal company 
113C

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()
        )
    )

 

 

 

 

 

1 REPLY 1
scee07
Resolver I
Resolver I

Hi, 

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

scee07_0-1700402010384.png

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 )
    )

scee07_1-1700403740600.png

Hope this helps. 
Best regards 
Christian

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors