Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
MonK
Regular Visitor

Reference to a dimension table after filtering

Hello everybody,

 

I have a DAX challenge here and hope anybody can help me out as I am unable to solve it.

 

My datamodel based on live-connect is a star schema looking like this:

 

MonK_0-1668243599759.png

 

The two dimensions contain several combinations from Category_IDs and Product_IDs. The dimension tables are connected with the fact table via a specific transactionID - which is named like the related dimension table.

 

 

For each combination there is a single value in the Dimension2, which I am looking for.

 

Now I have to filter for a value from dimension1, which means that the combinations of values from the table are filtered out.

There is a third dimension table which isnt shown here, but it leads to the details of the products and I have no problems with this, as the connecton is the Product_ID.

 

As the result I want to show a table at the end, which contains the

 

Category_ID and the

Product_ID,

Details from the Product_ID (table Dimension3) and the

value of the specific combination of CategoryID and ProductID from the dimension2 table.

 

Only the value of dimension2 is missing.

 

I used a measure with calculate and filter to filter the value from dimension 1.

Then I tried to create a virtual table as a VAR but afterwards I could not refer to this table in live connection.

 

Can anybody help me here how to proceed?

 

Thank you very much

best

MonK

 

 

4 REPLIES 4
MonK
Regular Visitor

Thank you very much for your reply.

It is not an 1:1 connection between the tables.

Dimension1 has several transactions with different Category/product combinations, the details of the specific transation is in the dimension1 table.

 

In Dimension2 are the details of another process, which includes the same category/product_IDs as in Dimension1.

 

Sorry if it is a little confusing. Can you help me here? Thanks a lot for having a look at this!

daXtreme
Solution Sage
Solution Sage

You should not create models with 1-1 connections between tables. Please consolidate into one single table because this is exactly what a 1-1 connection means: the tables are in fact one table.

oh sorry, now I know what you mean, in fact these are 1:n (dimension to fact) relationships.

Your model says something to the contrary... On top of that, it shows the relationships have two-way filtering enabled on them, which also should not be used lightly, only in certain well-defined circumstannces.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.