Forum Discussion

MonK's avatar
MonK
Regular Visitor
3 years ago

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:

 

 

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

  • daXtreme's avatar
    daXtreme
    Icon for Solution Sage rankSolution Sage
    3 years ago

    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.

    • MonK's avatar
      MonK
      Regular Visitor
      3 years ago

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

      • daXtreme's avatar
        daXtreme
        Icon for Solution Sage rankSolution Sage
        3 years ago

        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.

  • MonK's avatar
    MonK
    Regular Visitor
    3 years ago

    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!