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
