Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
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!
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.
User | Count |
---|---|
25 | |
10 | |
7 | |
6 | |
6 |
User | Count |
---|---|
30 | |
11 | |
11 | |
9 | |
7 |