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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PowerBIMaster97
New Member

Need help with using columns in two tables without inner joining them

Hello Everyone,

 

The issue I'm currently facing is: I need to use columns that exist in both tables (they have a matching ID column) in order to create some measures and a view. However, the rows in these tables are ALOT and when I tried to join them into one table that has all the columns I need, the processing is much too long.

 

Suppose I have two tables, A and B, and table A has colums of people's ids and their dates of registration, and table B has columns of people's ids and their status.

 

Table A
ID
Date

 

Table B

ID

Status

 

I want to be able to create a measure that can filter the ID's of people with a certain status for a given date. For example, A measure that shows All the IDs that have a status of "Away" for today. Basically, how can I filter columns in both of these tables using one measure without inner joining the tables?

 

Thanks in advance.

1 ACCEPTED SOLUTION
Barthel
Solution Sage
Solution Sage

@PowerBIMaster97 

You could use a virtual relationship in your measure using the TREATAS function. The TREATAS function is processed in the filter part of the CALCULATE function. For example, you can count the number of people based on Table A, taking into account the filters from Table B:

Number of people =
CALCULATE (
    DISTINCTCOUNT ( 'Table A'[ID] ),
    TREATAS ( VALUES ( 'Table B'[ID] ), 'Table A'[ID] )
)

 

View solution in original post

1 REPLY 1
Barthel
Solution Sage
Solution Sage

@PowerBIMaster97 

You could use a virtual relationship in your measure using the TREATAS function. The TREATAS function is processed in the filter part of the CALCULATE function. For example, you can count the number of people based on Table A, taking into account the filters from Table B:

Number of people =
CALCULATE (
    DISTINCTCOUNT ( 'Table A'[ID] ),
    TREATAS ( VALUES ( 'Table B'[ID] ), 'Table A'[ID] )
)

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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