DAX dynamic across columns

[Anonymous]

Hello everyone,

I'm trying to solve the following problem. I have two tables:

- Table A with columns Date, Commodity, product type, price type
- Table B with columns Date, average price, number of invoices

The number of invoices is a calculated measure, and I need to adapt it to the columns of the other table. There isn't a column that allows creating a relationship.

My measure:

Number of invoices =

CALCULATE(

    SUM(fatture_vw_rebound[count_of_invoices]))

My result should be like this example where I create a table, put the date created through a calendar, put a reference column, and my measure should adapt to that value.

In this example, my measure does not adapt to the reported COMMODITY column but only follows the adaptation of the date.

Thanks in advance. 

[TomMartens · Accepted solution]

Hey @Anonymous ,

because there is no relationship between the two tables, your measure does not adapt. Instead the count of invoices is repeated for each commoditiy. For this reason you have to adapt the measure in a way that it reflects the business rule.

Consider preparing a pbix file that contains sample data, but still reflects your model (tables, relationships, calculated columns, and measures), upload the pbix to OneDrive, Google Drive, or dropbox, and share the link in this thread. If you are using Excel to create the sample data instead of the manual input method, share the Excel file as well.

Do not to forget to describe the business rule that explains how the measure has to adapt and the expected result based on the sample data you provide.

Regards,

Tom