Forum Discussion
Connecting values from measures in different tables
Hello,
I have a measure in one table and I am trying to connect the values with a measure from another table. this is a continuation from my previous question https://community.powerbi.com/t5/Desktop/Sum-of-latest-values-of-all-groups/m-p/906426#M434408
The first measure is this (Gross Sales LE):
Gross Sales LE HELP =
VAR __myLastAmount = CALCULATE(LASTNONBLANK(Sales[Gross Sales LE (EUR)], ""), FILTER('Sales', Sales[EntryDate] = MAX(Sales[EntryDate])))
VAR __tmpTable = SUMMARIZE('Sales',[CustID], [Year], [ProductType], [Season],"__LastAmount",__myLastAmount)
RETURN SUMX(__tmpTable,[__LastAmount])
Gross Sales LE =
VAR __myLastAmount = CALCULATE(LASTNONBLANK(Sales[Gross Sales LE (EUR)], ""), FILTER('Sales', Sales[EntryDate] = MAX(Sales[EntryDate])))
VAR __tmpTable = SUMMARIZE('Sales',[CustID], [Year], [ProductType], [Season],"__LastAmount",__myLastAmount)
RETURN SUMX(__tmpTable,[Gross Sales LE HELP])The measure from the second table is similar, just called Net Sales. I would like to connect these measures based on fields CustID, Year, Season, ProductType which are in both tables. Is it somehow possible?
Below is some randomize sample data I created as an example (onedrive link):
https://1drv.ms/x/s!Ag_adS5VBxzvhps3bhu0eACgyRL1Tg
- Anonymous6 years ago
Measures are actually table independant. So you don't need to connect them at the measure itself.
Measures rely on the table relationships you already have, so if you data is already related you can just simply use measures together.
Measures can make use of inactive relationships, if you specify them with the DAX statement USERELATIONSHIP. Generally i use that as part of a CALCULATE statement.
3 Replies
- AnonymousNot applicable
Measures are actually table independant. So you don't need to connect them at the measure itself.
Measures rely on the table relationships you already have, so if you data is already related you can just simply use measures together.
Measures can make use of inactive relationships, if you specify them with the DAX statement USERELATIONSHIP. Generally i use that as part of a CALCULATE statement.
- AnonymousNot applicable
Thank you, properly connecting the tables together actually fixed my issues and then the measures were pretty simple to create.
- v-eachen-msft
Community Support
Hi Anonymous ,
Yes, it is possible. You could share your sample pbix file to us if you don't have any confidential information.