Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

 

  • Anonymous's avatar
    Anonymous
    6 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

  • Anonymous's avatar
    Anonymous
    Not 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.

    • Anonymous's avatar
      Anonymous
      Not applicable

      Thank you, properly connecting the tables together actually fixed my issues and then the measures were pretty simple to create.

       

  • v-eachen-msft's avatar
    v-eachen-msft
    Icon for Community Support rankCommunity Support

    Hi Anonymous ,

     

    Yes, it is possible. You could share your sample pbix file to us if you don't have any confidential information.