cancel
Showing results 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

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?

1 ACCEPTED SOLUTION
Solution Sage

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

Solution Sage

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