Column Summary Ignoring Filters

[millercj]

I've found a few posts on here loosly related but none really seem to apply to my model so here we go:

I've got Two Tables. Let's call them Transactions and Prices. Their architecture is as follows

----------TRANSACTIONS----------

ID#   |   EntryDate   |   TYPE

A43  |   8/14/2021   |  New Business

A44  |   6/14/2021   |  Repeat Business

A45  |   7/01/2021   |  Repeat Business

A24  |   5/21/2021   |  New Business

--------------------PRICES--------------------

ID#             |   TRANSACTION-ID#   |   PRICE

B4153135   |               A43               |     100

B4153133   |               A44               |     500

B4153187   |               A45               |     200

B4153145   |               A24               |     100

The common link is Transaction[ID#] to Prices[Transaction-ID#]

No fields are measures and the are coming directly from an SQL database if that matters

I have a visualization that shows the values Transaction[ID#] and Prices[Price] which correctly displays in this fashion.

ID#   |   Price

A43  |   100

A44  |   500

A45  |   200

A24  |   100

Total     900

The problem is that when I apply any filter (I'm tyring to Filter Transactions[Type] and Transactions[EntryDate]), the rows are properly filtered however the total continues to show 900 as opposed to the filtered total.

Any help woud be great!

[v-yalanwu-msft]

Hi, @millercj ;

Is the above answer helpful to you? If so, Would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yalanwu-msft]

Hi, @millercj ;

According to your description, I tested it. First of all, I confirmed that the two tables are in Directquery mode. Secondly, if the two tables are not related, it will not work. There must be a relationship. The filtering direction is best to be bidirectional, as follows:

The final output is shown below:

If it still does not solve your problem, can you share more scenes or information about your table?

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

Create a third table with a single column which lists down all unique ID's from both tables.  Create a relationship between from the ID column of both tables to the newly created third table.  To your visual, drag ID from the third table.  Write this measures:

Measure = sum(Prices[Price])

Hope this helps.

[Greg_Deckler]

@millercj I am not able to replicate the behavior. See attached PBIX file below signature.

[millercj]

Looks like chaning "Cross Filter" to "Both" is what made the difference