Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper I
Helper I

Get lookup from other table struggle



I'm sure this will be easy points for the forum gurus.


Table A ("Orphaned Workspaces"): Contains PBI workspace information. Has a `WS_ID` information. 

Table B ("Workspace History"): Contains PBI workspace activity information. Has a `WORKSPACE_ID` that links to table A. Has a `UserId` field that links to table C.

Table C ("AAD_USERS"): Contains AAD Users. Has `Principal_User_Name` that links to table B.

In model:
TableA has a relationship with TableB.
TableB has no relationship with TableC, but can be filtered by the UserId through Principal_User_Name.

What I would like to get is the lookup  into the TableC and return TRUE / FALSE if the userid from workspace history exists in AAD_Users table.. (TABLEC). The idea is to identify if all the users are still in the company or not.


Thank you!

-- @sjoerdvn (easy points for you perhaps?)

Resolver III
Resolver III

try something like:

aad_user_lookup = IF(HASONEVALUE('Workspace History'[USERID]),
  CONTAINS('AAD_USERS',[Principal_user_Name],VALUES('Workspace History'[USERID])))

Thank you for your quick response. However this is not working as it ends in visual complaining there is more than 1M rows.

just for the picture: Workspace History is de facto Activity Events table with millions of rows. I'm expecting the TableA (workspaces) to filter TableB (ActivityEvents) naturally by using the relationship, which works relatively fine. I'm saying relatively as when I click on a record in the WorkspaceHistory visual, it gets me 1M rows error too...

But adding the DAX you've proposed doesn't unfortunately work.

Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors