Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Everyone, Perhaps I'm over analyizing the problem with my model as I'm sure there must an efficient way of handling this scenerio.
The issue is that my fact table contains many columns that all relate back to the customer table (customer ID), the catch is that they are in different contexts. For example in the fact table, An order would have a Ship_toCustomerID that refers to a customer in the customer table, as well as a BillToCustomerID that also refers to a customer in the customer table. Power BI allows for a single primary key in the join along with surrogate keys, however If I try to present customer information for ShipTo and BillTo, I only get customer attributes back based on how the primary key has been set in the Power BI model. I must be missing something.
Solved! Go to Solution.
I would like to thank everyone who contributed to this topic. Considering your excellent suggestions along with several hours of research, My conclusion is this...
In power BI we have three ways of addressing the issue of multible relationships between two tables, the best solution being based on your specific situation and requirements. Factors like.. amount of data & the need to show dimensions as text in the result rather than as part of a calculated measure.
Each of these methods have been detailed earlier in this thread.
This topic was also explored in an articale from ExceleratorBI
https://exceleratorbi.com.au/multiple-relationships-between-tables-in-dax
I'm faily new to Power BI, but have deep experience in most other BI tools, I'm dissapointed that Microsoft suffers from the same handicap as the rest. If they could find a more elegant solution, that would certainly put Power BI in a class all by itself.
I would like to thank everyone who contributed to this topic. Considering your excellent suggestions along with several hours of research, My conclusion is this...
In power BI we have three ways of addressing the issue of multible relationships between two tables, the best solution being based on your specific situation and requirements. Factors like.. amount of data & the need to show dimensions as text in the result rather than as part of a calculated measure.
Each of these methods have been detailed earlier in this thread.
This topic was also explored in an articale from ExceleratorBI
https://exceleratorbi.com.au/multiple-relationships-between-tables-in-dax
I'm faily new to Power BI, but have deep experience in most other BI tools, I'm dissapointed that Microsoft suffers from the same handicap as the rest. If they could find a more elegant solution, that would certainly put Power BI in a class all by itself.
Thanks, This would be perfect if this was a calculation. However the request that I have is to simply show the names of the customers side by side in a table view by order number. for example BilltoCustomer & ShiptoCustomer names.. Most of the examples that I found are how to use USERELATIONSHIP with an expression like SUM. I'm still learning DAX, so there is probably some simple expression for this that I have yet to find.
I think what you need is only a relation between the two tables on customer_id. All the id's in your fact are for combining the data with multiple dimensions. So, if you want to combine the Sales Amount with attributes from the customer, you should join only by customer_id. And remove the other joins between these two tables.
For Example:
Table A (= fact):
customer_id
bill_to_customer_id
product_id
Sales ($)
Table B (= dimension):
customer_id
name
job
division
If you want to have the name (Table B) with the Sales amount (Table A) it is enough to join only on the customer_id.
They call this "Dimensional Modelling".
The issue that I'm trying to solve has to do with contextual reference. If for an order you have a Customer and a BillToCustomer (that are not the same) and join to a customer table in a single contexted join (i.e. FactCustomerID to DimCustomerID). Your going to return the value based on the nature of the context referenced in the join. In other BI tools, they solve this by breaking the dimension out into several tables to support each context. In the data world we would just form a unique key, however Power BI seems to approach this in a different way. The use of USERELATIONSHIP will solve for a measure, but I'm just trying to present the data in a table as is. USERELATIONSHIP requires an expression to work. so I just need to find an expression that presents the data without doing any sort of summation.
@heavyarmswrote:In other BI tools, they solve this by breaking the dimension out into several tables to support each context.
You can do this in the Query Editor. Reference (or duplicate) your table from source as many times as you have contexts and then filter each new table and create your keys in the fact table.
That method of modeling is very inefficient, so I would like to avoid it if at all possible. There must be a better answer than to just create 6 different customer tables just to show for any Order a BillToCustomer next to a SoldToCustomer.
@heavyarms,
You can unpivot your columns in the sales fact table, then create relationship between the sales table and customer table. For more details, please review the following screenshots.
Regards,
Lydia
Wouldnt that require that I define the context in DAX everytime I need to visualize data that is outside of the primary key? I was hoping for something that could be applied in the model to acheive this.
Build a few measures, try it out.
What luck have you had with USERELATIONSHIP() https://msdn.microsoft.com/en-us/library/hh230952.aspx ?
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |