Get a measure from a related table for comparison

[dimitrishuk]

Hi all,

I'm trying to build out a Tabular model for our accounts receivables and havings some difficulties getting the relationships/dax right for one specific measure.

As you can see I have my Facts table 'Accounts Receivable Ledger', where most measures exist and a number of dimension tables around these. 

Case in point 'Accounts Receivable Ledger' contains a measure for 'Total Open' which works fine and looks like this. Its basically a full SUM of all invoices and receipts up to MAX('Date Dimension'[Date]). The DAX formula is shown below.

Total Open:=
IF(
     MIN('Date Dimension'[Date])<=CALCULATE(MAX('Accounts Receivable Ledger'[Transaction Date]),ALL('Accounts Receivable Ledger')),
     CALCULATE(
          SUM('Accounts Receivable Ledger'[Amount - Gross]),
          FILTER(ALL('Date Dimension'[Date]),'Date Dimension'[Date]<=MAX('Date Dimension'[Date]))
))

As you can see below the 'Accounts Receivable Ledger'[Address Number] is then related to another Dimension table 'Address Book'[Address Number] in a many-to-one relationship with bi-directional filters on. After that there is another table called 'Customer Master' which has a one-to-one relationship with [Address Book].

I would like to have a measure contains the 'Customer Master'[Amount - Credit Limit] so that I can compare it to 'Accounts Receivable Ledger'[Total Open] and determine whether someone if over their credit limit. I would also like this measure to work only at the level of all dimensions in the 'Address Book' as this is a per account measure, but not in dimensions within the fact table 'Accounts Receivable Ledger' (for example the analysis of amount open vs credit limit on a per invoice basis is not useful).

I have put the following formula into the 'Accounts Receivable Ledger' table:

Credit Limit:=CALCULATE(SUM('Customer Master'[Amount - Credit Limit]),ALLEXCEPT('Customer Master','Customer Master'[Address Number]))

The problem is I am getting blank results when placing 'Address Book' dimensions into the rows. I would l

Can anyone help?

[v-yuta-msft · Accepted solution]

Hi dimitrishuk,

According to your screenshot, it seems like your table 'Accounts Receivable Ledger' and table 'Customer Master' are many-to-many relationship. You can't put columns from two "many" sides into a same chart because the two column can't match with each other in every rows.

Regards,

Jimmy Tao

[Greg_Deckler]

Would probably need sample data to mock this up to troubleshoot. I'd simplify the problem down to just the bare essentials, replicate the problem and then post sample data or the PBIX for people to take a look at and troubleshoot. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490