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
Bharathi_99
Helper IV
Helper IV

LookUp

Hi all,

I have ContID in Dim table and I have ContID in Fact table,
I want to get only those ContID's in dim table into fact table

Instead of Inner Join on ContID's from two tables I weanna do this

Please help

Thanks

7 REPLIES 7
v-zhangti
Community Support
Community Support

Hi, @Bharathi_99 

 

Can you provide sample data for testing? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures or Excel. 

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

AmiraBedh
Resident Rockstar
Resident Rockstar

To filter the `ContID` in the Fact table based on the `ContID` in the Dim table using DAX, you can use the `FILTER` function.

Let's say your tables are named `DimTable` and `FactTable`.

You can create a new column or table in your Power BI model to achieve this filtering. Here's an example of how you can achieve this with a new column:

Filtered ContID =
IF(
CONTAINS(DimTable, DimTable[ContID], FactTable[ContID]),
FactTable[ContID],
BLANK()
)

This formula checks if the `ContID` in the FactTable exists in the DimTable. If it does, it returns the `ContID` value. If not, it returns a blank.

If you prefer to create a new table instead of a column, you can use the `FILTER` function like so:

Filtered FactTable =
FILTER(
FactTable,
CONTAINS(DimTable, DimTable[ContID], FactTable[ContID])
)

This new table (`Filtered FactTable`) will have only the rows where the `ContID` in the FactTable exists in the DimTable.

Be careful with using this kind of filtering over large data sets as it might have an impact on performance. Always test your solution in the real-world scenario to make sure it performs well.


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696
Cbutler
Helper III
Helper III

Could use a Lookupvalue calculated column? This will bring the value from one table in to the other, no relationship required. 
LOOKUPVALUE – DAX Guide

Might be, I have doubt on this

I just want to filter Id's in 2nd table for the id's which are present in 1st table

Can you please provide more details?
I already provided an answer.


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

Hi,
Actually in SQL lvel I was doing Inner Join based on ID  IN fact on dim table, it's giving lot of refreshing issue
data size is like 38M, so it's taking 3.5hrs to refresh
So I need alternate sol for that

Can you please provide sample data ?


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

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