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
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
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.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors