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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
pacifist
Helper I
Helper I

Get lookup from other table struggle

Hi, 

 

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.

pacifist_0-1694678180589.png


Thank you!


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


2 REPLIES 2
sjoerdvn
Super User
Super User

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

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.