Relationships and Lookupvalue - Calculated Measure, Not Column

[Anonymous]

Hi,

I have 2 different data source - one called DV360, the other called DCM.

The DV360 data is as follows:

It has an ID and a row for each day with a Total Media value. There will only be one for for every day for each ID.

The DCM table is as follows:

The DCM table has an ID that can be linked to the DV360 as well as a date field. But there will be several rows for each day and each ID. The DCM table is also linked by a many to one relationship to a calendar table that sits over a

I want to be able to combine the data to show the Total Media value linked to the DCM table but I don't want this value duplicated for each row, which would happen with a calculated measure or by merging the queries. So I'm thinking I would use a calculated measure but I don't know how to go about it.

At the moment there are no relationships in place between the DV360 and DCM tables. I can make a many to many connection between them based on ID but I can't then make the data filter by date as the DCM table is linked to the Calendar on the date field.

Any suggestions?

Thanks,

MarkJames

[v-xicai · Accepted solution]

Hi @Anonymous ,

Yes, you can create many to many relationship between DCM table and DV360 table based on ID , create relationship between DV360 table and Calendar table on the date field, then For all the relationships, try to change the Cross filter direction from Single to Both, which will treat the three tables as a single table, so you needn't create relationship between DCM table and Calendar table.

You can create measure like DAX below to meet your demand.

Measure1= CALCULATE ( FIRSTNONBLANK ( [Total Media value ], 1 ), FILTER ( DV360 , DV360 [CM Placement ID]= MAX(DCM [Placement ID])))

Best Regards,

Amy

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