Complicated Measure filtering multiple columns from different tables

Nicci — Wed, 28 Jun 2023 15:37:21 GMT

Hi All, I'm in need of some severe help. I've been stuck on this issue for a few weeks now 😞

I have a bunch of tables with financial data but here are the main ones:

Table 1 - List of expenses by purchase order number and what FY it expires.

Table 2 - Total cost for all purchase order numbers, FY it was purchased in, and a flag if it's a blanket order or not.

The 2 tables are relationshiped on the purchase order number.

Scope:

1. Have the total purchased for the FY chosen from the slicer from Table 2.

2. Have a total of purchase orders from Table 1 that are expiring next fiscal year which will be the FY after the FY chosen in the slicer and is NOT a blanket order (flag and totals live in table 2)

3. Have the total of #1 and #2 combined for final total needed.

I am able to do #1 in the scope. I have a card visual that sums the total from Table 2. The slicer takes care of the filter.

I also was able to create a card for #2 but not without some heavy filtering on the card itself. My issue here is because I'm filtering the visual I do not know how to take the outcome of the filters so I can make a 3rd card that totals card 1 and 2 together. I cant add the same filtering on the 3rd card because some of those filters will remove totals from scope #1.

Help! LOL

Thank you!

Re: Complicated Measure filtering multiple columns from different tables

technolog — Sun, 03 Sep 2023 17:36:52 GMT

For the first scope, you've already managed to get the total purchased for the FY chosen from the slicer from Table 2. That's great! Let's call this measure TotalPurchasedFY.

For the second scope, you want to get the total of purchase orders from Table 1 that are expiring next fiscal year, which is the FY after the one chosen in the slicer, and is NOT a blanket order. Let's create a DAX measure for this:

TotalExpiringNextFY =
VAR CurrentFY = SELECTEDVALUE('Table 2'[FY])
VAR NextFY = CurrentFY + 1
RETURN
SUMX(
FILTER(
'Table 1',
'Table 1'[ExpiryFY] = NextFY
),
CALCULATE(
SUM('Table 2'[TotalCost]),
'Table 2'[BlanketOrderFlag] = 0
)
)
In this measure, we first determine the current FY from the slicer and then calculate the next FY. We then filter Table 1 for rows where the expiry FY matches the next FY. For each of these rows, we sum the total cost from Table 2 where it's not a blanket order.

Now, for the third scope, you want to combine the totals of the first two scopes. This is straightforward:

FinalTotal = TotalPurchasedFY + TotalExpiringNextFY
You can now use the FinalTotal measure in a card visual to get the combined total of the first two scopes.

topic Complicated Measure filtering multiple columns from different tables in DAX Commands and Tips

Complicated Measure filtering multiple columns from different tables

Re: Complicated Measure filtering multiple columns from different tables