Disconnected table measure not grouping as expected

[macfuller]

I would like help discovering why a disconnected slicer is not returning the expected data. Hopefully I’m missing something obvious.

I would like to return a payment schedule based on our Accounts Payable (AP) groupings. The days to pay bucket is the Payment Date (if one exists) - Accounting Date (what we call the Invoice Date).

	Nov 2017	Dec 2017	Jan 2017
< 7 days	$$$	$$	$$
7-10 days	$	$	$$
11-14 days	$$	$$$	$$
etc

<tbody>
</tbody>

I have a disconnected table ‘AP Intervals’ with the AP text groupings along with Min and Max columns for the number of days in the group. For example

Duration	Min	Max
< 7 days		7
7-10 days	7	10
11-14 days	10	14
etc

<tbody>
</tbody>

Our vouchers table has the following structure. Note that the full check total is returned by our AP system for each row so I use the Voucher total rather than Check total.

Invoice No	Accounting Date	Voucher Amount	Check No.	Payment Date	Check Amount
A01	6/7/17	$40	001	6/20/17	$100
A02	6/12/17	$20	001	6/20/17	$100
A03	6/13/17	$40	001	6/20/17	$100
etc

<tbody>
</tbody>

These are the relevant measures I am using.

Avg Voucher Pay Days:=
[SIZE=1][COLOR=#008000][SIZE=1][COLOR=#008000]AVERAGEX[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=1](Vouchers,[/SIZE][SIZE=1][COLOR=#008000][SIZE=1][COLOR=#008000]IF[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=1]([/SIZE][SIZE=1][COLOR=#008000][SIZE=1][COLOR=#008000]ISBLANK[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=1](Vouchers[Payment Date]),[/SIZE][SIZE=1][COLOR=#008000][SIZE=1][COLOR=#008000]BLANK[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=1](),Vouchers[Payment Date]-Vouchers[Accounting Date]))[/SIZE]

Paid Amt:=
[SIZE=1][COLOR=#008000][SIZE=1][COLOR=#008000]CALCULATE[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=1]([/SIZE][SIZE=1][COLOR=#008000][SIZE=1][COLOR=#008000]SUM[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=1](Vouchers[Voucher Amount]),Vouchers[Check Amount]>0)[/SIZE]

Payment By Period:=CALCULATE (
    [Paid Amt],
    FILTER(
         'AP Intervals', 
         [Avg Voucher Pay Days] >= 'AP Intervals'[Min]
        && [Avg Voucher Pay Days] < 'AP Intervals'[Max]
        )
)

When I create the pivot table and use the ‘AP Intervals’ [Duration] as the row and [Payment By Period] as the value the measure returns the same total for all the AP Interval categories instead of grouping it. I'm not sure if it's something about the Average measure that is causing the difference but I've used this format in the past with no problem. Perhaps I need to group by the Check Number but I'm not sure how the measure would be created.

 

[gazpage]

I may misunderstand what you are doing here, but I’m not sure how the last measure is meant to work. You are filtering the Ap Intervals table and then applying that to the Paid Amount Measure. But the AP intervals table is a disconnected so applying a filtered version of it to your measures won’t do anything.

 

[macfuller]

I thought it would be like applying a histogram where it counts/sums the number of items within that interval and assigns them to a particular AP "bucket". Histogram logic works along the same lines. So for items invoiced in November I would sum how many payments fell into each bucket.

 

[gazpage]

Right. Filter the main table rather than ap interval table then.

Look up static segmentation or dynamic segmentation on SQLBI.com.

 

[macfuller]

Thank you. I'm not sure how I missed that combination but it works.

Payment By Period:=CALCULATE (
    [Paid Amt],
    FILTER(
         Vouchers, 
         [Avg Voucher Pay Days] >= MAX('AP Intervals'[Min])
        && [Avg Voucher Pay Days] < MAX('AP Intervals'[Max])
        )
)

It's pretty slow though. If I create a custom column and put the Payment Date-Accounting Date into it, then use that instead of the [Avg Voucher Pay Days] measure it adjusts much faster.

 

[gazpage]

Well, if you are going to do that, you might as well put a calculated column in that calculates your categories, e.g.

<code class="dax plain">Payment by period:= <code class="dax color2">CALCULATE</code> <code class="dax parenthesis">(</code></code>
<code class="dax spaces">    </code><code class="dax color2">VALUES</code> <code class="dax parenthesis">(</code> <code class="dax plain">'AP Intervals'[Duration] </code><code class="dax parenthesis">)</code><code class="dax plain">,</code>
<code class="dax spaces">    </code><code class="dax color2">FILTER</code> <code class="dax parenthesis">(</code>
<code class="dax spaces">        </code><code class="dax plain">'AP Intervals,</code>
<code class="dax spaces">        [Avg Voucher Pay Days] >= MAX('AP Intervals'[Min])</code><code class="dax plain"> </code>
<code class="dax spaces">        </code><code class="dax plain">&& [Avg Voucher Pay Days] < MAX('AP Intervals'[Max])</code>
<code class="dax spaces">    </code><code class="dax parenthesis">)</code>
<code class="dax parenthesis">)</code>

Then you have a column you can do regular filtering by.