How to make mesaure work on multiple lines or have columns react to filters?

[TCR_Carlsberg]

Hi you more experienced Power BI users

I can't get a measure to calculate my values correct so if someone could explain what I am doing wrong that would be much appriciated. I have created a very simple testcase - don't look like I can attache that file to this message thou... I'll try to display what I have in the text and pictures.

I have 5 tables. 

Product, Customers and Campaigns
Then two relational tables connecting the Campaigns with Customers and Products.

In the Campaign table I have a measure that calculate the max score that is Customers x Products. This measure works fine on a row level but as you can see above it fails when looking at more than one campaign at a time.

MeasureTotScore = Campaigns[CountCustomers]*Campaigns[CountProduct]
CountCustomers = COUNTROWS('RelationCampCust')
CountProduct = COUNTROWS('RelationCampProd')

If I instead create a Column with the same formula it works fine for one or more Campaigns but then the values dont react to Filter changes. Again looking at the picture - if I select Product type A I want that to reflect in my numbers. This is what I get:

Again on a single row the measure does it job but completely fails when looking on multiple lines. And the calculated column isnt affected at al of my selection. The result I want is 12 in my example above.

Any pointers for a newbi?

Regards,

Thomas

[v-yulgu-msft · Accepted solution]

Hi @TCR_Carlsberg,

In my test, I named the three tables as 'Products', 'Customers' and 'Campaigns'. Please try below measures:

CountCustomers = DISTINCTCOUNT(Campaigns[CustID])
CountProduct = DISTINCTCOUNT(Campaigns[ProdID])

MeasureTotScore = SUMX(Campaigns, Campaigns[CountCustomers]*Campaigns[CountProduct])

Best regards,

Yuliana Gu