Dynamic filter expressions

Suppose there is a huge fact table with lot of orders…now there is another table that calculates commissions… but it has something like this…

the fact table has customer, orderdate, product, quantity, etc…

commissionProfile commission expression
P1 0.5 orderdate > ‘1-1-2019’
P1 1 orderdatedate <= ‘1-1-2020’ and product = ‘Mangoes’
P2 5 quantity > 100

Each customer is associated with a particular profile and we want to calculate commissions for each customer.

I have greatly simplified the structure of the table but this setup lets us have a lot of flexibility in commission schedule.

Is there a way to do it in powerbi?

Hi @speters,

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?


sampleDNA.xlsx (9.1 KB)

I have attached a very simple data tables

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.

Have you thought about unpivoting the data.

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.


1 Like

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.

I am not exactly sure what you mean…the condition columns are expressions and I am not sure how to dynamically apply them.

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

Sam, thanks for taking the time to respond.

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…

You just don’t need to do that. All you need is to create measures and then use the measures in your reports within visualisation and tables.

I think this maybe is over complicating it.

Have you gone through the below courses? Everything is covered in detail around how to work with measures etc.

All of these calculations can also be done with iterating function quite easily once you understand how these work.

Have you learnt about these yet? Like SUMX?

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!

Hi @speters, a similar topic was covered in this forum thread link. You might get additional tips here