How to use different Filter criteria per line

[jasi]

Hi,

I'm stuck with a problem and hope soemone here can help me out. I treid to simplify the setup to point out the issue.

I have a lookup table containign Advertiser / Campaing /start and end date

The data table only has Advertiser date and value. My goal is to get the Values per live campaign (data date is in between start and end lookup date).

My idea was to have a a calculated column in the data table indicating whether the line is belonging to a "live" campaign (0/1)It would be soemthing like this in in the filed.

Lookup corrospondind Advertiser in th lookup table, check whether date is within start and end dates, if yes 1 if no check for next line with corrosponding advertiser, cehck dates ...

But I was not able to get the proper DAX. Any ideas?  I'm open to other solution as well.

Thanks

 

[JordanAWARE]

SO assuming i understand what you wanting..

the data table you want to add in the Campaign column based on the advertiser and date?

make a new calculated column in the data table that does this:

Campaign = 

IF ( 'lookup table'[Advertiser] = 'data table'[Advertiser] & 'data table'[Date] >= 'lookup table'[Start Date] &

'data table'[Date] <= 'lookup table'[End Date],

'lookup table'[Campaign],

blank()

)

then you can just create a measure to count value total

Total = count(value total)

then make a table on the report, drag in campaign, advertiser, total 

[jasi]

Thanks for your help, but it is not to working for me.

I get the error already when PBI should compare the Advertiser fields. It is not doing the many to many compare.

IF ( 'lookup table'[Advertiser] = 'data table'[Advertiser] ,0,1)

"This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max"

Any other idea?

[v-qiuyu-msft · Accepted solution]

Hi @jasi,

You can create a measure in Lookup Table below. See attched .pbix.

Measure = CALCULATE(SUM('Data Table'[Value]),FILTER('Data Table',MAX('Lookup Table'[Advertiser])='Data Table'[Advertiser] && MAX('Lookup Table'[End Date])>='Data Table'[Date] && MAX('Lookup Table'[Start Date])<='Data Table'[Date]))

Best Regards,
Qiuyun Yu

[jasi]

Thanks a lot @v-qiuyu-msft

This is working out fine for this example. I was able to recreate it.

I will now try to apply your solution to the more complex live data!

cheers

jasi