IN Operator, array in field value

[AgencyPowerBi]

Hi,

I want to do a conditional sum by using IN operator.

I have clients for which I calculate the sum investments depending on the period and type of investments.

So for instance, 

Client A

active from 1.1. 2018

Active to 31.1.2018

Media PRINT, TV, OOH

These would be stored in 4 columns of a table

Client | AcitveF | ActiveT | Media

text | date | date | text

So, is it possible and how to caluculate sum in a way

calculate (sum [Cost]; start date > [activef] && end date < [activet] && media IN [Media]

last part is making me problems since IN operator wants an array.

Can I make one from a text field?

Tx 

[v-chuncz-msft]

@AgencyPowerBi,

Instead, you may try SUBSTITUTE Function and PATHCONTAINS Function.

[AgencyPowerBi]

So something like:

calculate (sum [Cost]; start date > [activef] && end date < [activet] && pathcontains([media]; media) ?

tx,

Ante

[AgencyPowerBi]

Tried it, dodnt work.

I'm including excel in which Im trying to implement this measure.

Advertiser sheet is data for advertisers: Name of advertiser, and list of media it is active in.

Cost is just advertisers, media and cost columns.

What I want is to calculate the cost if media in Cost table coresponds to list of media for the advertiser.

Excel file