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
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.
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.
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.
Can you please provide more details?
I already provided an answer.
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 ?
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.