It would make it better to understand if you had some sort of sample data, but I have a few questions.
Are P1 and P2 in your example the particular profile ID’s? And if so, could a customer fall into multiple profiles? Do you calculate with 0.5 when a customer has profile P1? How are the fact table and commissions table connected?
There is a one to many customer to commission profile… One customer can have only one commission profile. But multiple customers can have the same profile.
So you then have column which are p1, p2, p3…something like this.
This way you then have your customer and date and value all on the same row and then can create simple relationship back to your date table and customer table.
Hi @speters, we’ve noticed that no response has been received from you since 28th of February. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. You may reopen a new thread when the need arises.
In other words, if a table has some expressions - would it be possible to pass that to other measures for instance Calculate([Total Sales], expr from a column)?
If I’m understanding this correctly you’re saying that you have measures within calculated columns.
You really want to avoid this if you can. From my experience you almost never with fact tables need to create calculated columns with formula logic inside them.
The idea is that you need to structure your table effectively and build the right relationship around it to supporting tables to then create DAX measures which work out this calculation for you.
Based on what you were saying earlier in this thread was that you had three rows in a table which were basically for the same transaction. So what I’m saying is that you should attempt to unpivot that so you have one row for each transaction in your fact table
Here are some links to review
See how you go with these.
To me this is clearly a data setup issue first and foremost. Once you get this right the actual calculations you need to do might not be that difficult
let’s say I want to calculate different measures -> for instance Sum last month, sum of orders > 10K, sum of orders < 100K, sum of orders over 70K this month… and these expressions can be complex and can be lot. Of course, at the time of creation, I can create these manually. But instead can I create a table with these expressions… and then pass the expression from the column to the measure thereby creating a dynamic measure… does this make sense?
The basic measure would be sum(Amount)… but then branches would be based on the expressions in the column - for instance calculate(sum(amount), Order[Amount] < 100) etc…
A response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!