I have created a measure which multiplies the no of the customer of different type and the price customer want to pay w.r.t season of the year and time of the day.
WTP =
Var _agri = CALCULATE([WTP Agr] * [Agricultural])
Var _com = [WTP Com] * [Commercial]
Var _ind = [WTP Ind] * [Industrial]
Var _res = [WTP Res] * [Residential]
RETURN
_agri + _com + _ind + _res
However, I noticed that when I select the season the year from slicer it gives the same value for the all time of the day for the season. I am not sure where am I making the mistake. Whether its data model or problem in dax calculation. Any help in fixing the issue would be really appreciated. Sample here sample_WTP.pbix (6.7 MB)
I didn’t bother spending much time reviewing the DAX, since one look at the data model unfortunately tells me that until the model is completely rebuilt from the ground up, the DAX could be perfect and still would return complete nonsense results.
In just my initial review of the model, I counted at least 10 major flaws/modeling violations, any one of which could sink the accuracy of the DAX calculations. At this point, fixing the model requires a complete conceptual review and reconfiguration – far beyond the scope of a single forum question (or even a reasonable series of questions).
My advice at this point would be to go back through the entire EDNA Data Transformations and Data Modeling course, paying particular attention to revising this data model in accordance with a standard star schema.
At that point, repost your revised PPIX and we can give you a read on whether it’s then within the scope of the forum, or whether you will need some outside consulting assistance.
Nice work – much improved. Still some issues to work out, but now within the scope of what can be done within the forum. If you can please provide me the underlying dataset, I will work through the additional modeling changes necessary within Power Query to get reliable DAX results.
The file (usually Excel or CSV) that you used to populate the tables in your data model. I need that file in order to be able to go into Power Query and make changes to the current data model.
You are still a ways away from the optimal star schema model, but at the end of the day the critical question about your data model is does it support do the analysis you want/need to do? If the answer is yes, then that may be good enough even if it’s not technically optimal.
Obviously, you’re the only one who can make that decision. But if you run into any trouble down the road, you always know where to find us…
the 1:1 bidirectional relationship is likely going to cause problems and is unnecessary. That should be converted to a 1:M unidirectional relationship.
despite the fact that WTP varies by customer type, there currently seems to be no connection between the WTP table and the cutomer side of the model. It seems there should be a relationship between the sde customer_entry_point_evw table and the WTP table, so that the WPT table results are sliceable/filterable by the dimensions of the customer-related table.
this is more a personal preference than an error correction, but I would remove both of the bridge tables and replace them with virtual relationships built via TREATAS to solve the many-to-many problem associated with connecting Date and WTP by Season and Time and WTP by time of day.
At that point, you will have a straightforward 3 dimension table, 2 fact table star schema compliant model optimized for DAX.
