Forum Discussion
How to create a dynamic table? I got the error that column was not found in the input table :(
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