How to calculate different columns based on multiple rules

[AgencyPowerBi]

Hi, I have a problem which is two fold:

1. I have huge table with different target columns, that I would unpivot and use one column.

           Problem is that I have too many rows, which makes upivoting impossible.

2. I need to calculate values from these tagret colums in one calculated column based on multiple conditions.

           Different tagets are used for the same client based on time.. so in 2018. I need to take value from one column, in 2019. for the same client active is another column.

So I have defined client table, with start and end dates and name of the column used in that period, but I cannot substitute text for the name of the column. Or can I?! 

Is there a way to do this?

Thank you 🙂

[amitchandak]

@AgencyPowerBi , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Also, check of calculation groups can help

https://www.youtube.com/watch?v=vlnx7QUVYME

https://www.sqlbi.com/blog/marco/2020/07/15/creating-calculation-groups-in-power-bi-desktop/

[AgencyPowerBi]

Hi,

I have attached excel file with three tables.

Table sheet is main table with all data.

Buying_Channel_def is table that determines name of the Channel that goes into Buying_Target_def table.

Buying channel and Buying target are connected with a relationship, as well Table (connects to Media column).

As you can see there are few conditions that have to be met. Start time determines Buying channel name, and then Date determines the name of the column that would serve as a source to calculate sum.

Table sheet cannot be unpivoted beause its number of rows 😞

Calculation groups, I just started using, so I see no way to make this calculation.. If you know a way.. 

Many thanks!

Table 

[v-rzhou-msft]

Hi @AgencyPowerBi 

I see the three tables you are dealing with, and I am confused about the data model and your calculate logic.

Please show me the relationships between three tables. And your Table is blank, I couldn't see the value in Media column.

Can you explain your calculation logic to me in more details? Just like firstly we will get result or filter by the value from which table, and then and finally got the result. It is better for you to show me the logic by specific data. You can show me a screenshot of the result you want.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[AgencyPowerBi]

Ok, so basically, I need to make a conditional column that would take values from Ad or Bd depending on Media, SDate and STime column. 

Standard conditional column can take use just one condition, and I need three things to consider. It would be easier if I could unpivot Ad and Bd colums, but it is not possible.

Can you give a solution that would be scalable to millions of rows? 

Tx

p.s.

The thing I wrote would be easy with custom column and simple if else formula, but I have no Ad and Bd column only, I have at least 10. So, formula would be very complicated and messy. I thought that I could address column with a string value, but it seems impossible..