topic Re: Reference to a dimension table after filtering in DAX Commands and Tips

Reference to a dimension table after filtering

MonK — Sat, 12 Nov 2022 09:24:25 GMT

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

Re: Reference to a dimension table after filtering

daXtreme — Sat, 12 Nov 2022 11:04:50 GMT

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.

Re: Reference to a dimension table after filtering

MonK — Sat, 12 Nov 2022 11:22:55 GMT

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!

Re: Reference to a dimension table after filtering

MonK — Sat, 12 Nov 2022 11:27:02 GMT

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

Re: Reference to a dimension table after filtering

daXtreme — Sat, 12 Nov 2022 12:06:32 GMT

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.