Filtering the Bar chart containing Use relationship Measure - Not filtering data correctly

[Anonymous]

Hi Team,

I have a model in which there is one data table that contains invoice date and payment date. The active relationship is invoice date. I have created a measure called as Payment date as: 

Payment by Payment Date = CALCULATE(SUM(Main[PaymentAmount]),USERELATIONSHIP(Dates[Date],Main[PaymentDate]))

With that I have created the bar chart for Payment by month and there is a bar chart with Invoice with month. The data shown in the barchart seems to be correct. But, when I click the one of the month in the Payment barchart it is not filtering the table as I think it supposed to. It is filtering as if I am clicking on Invoice date (being the main active relation). One payment can have multiple Invoices. So when I click Payment month then it should split the invoice months and vice versa but it is only showing one month. Also, when clicking the payment amount apr 2017 (2.0M) the table is fitlering by Invoice month (9M) which should only  those records summing to 2 M.Hope it makes sense. 

I will attach some pictures and let me know if it is a bug or I am doing something wrong. 

First Look - Invoice and Payment together

Clicking Invoice month- Only filtering Payment for that month

Click Payment month - Only filtering Invoice for that month

Clicking Invoice Month - Table filtered by Invoiced month (Check amount)

Model

[v-lili6-msft]

HI  @Anonymous 

this is an expected behavior in power bi for your data model.

from your screenshot, all your data are in one table, and are only connect with date table, so when you click one month bar, it will only pass year-month as a filter to other visual.

you need to change your data model in your report, please share some simple sample data and your expected output and we could give some suggestion for your model.

Regards,

Lin

[Anonymous]

Thanks Lin for your quick reply. Please find my sample data below. 

Sample Data

[v-lili6-msft]

hi  @Anonymous 

do not use a screenshot for sample data that will will be difficult to import into power bi on our side.

Some simple sample data and expected output would help tremendously.
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

Regards,

Lin

[Anonymous]

Sorry Lin! Thanks for the suggestion, I will keep that in mind.  Please find it below. One payment can have multiple invoices. If possible I want to be able to filter the data both by Invoice date and Payment date but the main one we are after is Invoice. Since, clicking on bar chart for the Payment did not filter the table as expected my question has emerged. 

VENDORID	VENDNAME	PaymentVoucherNo	PaymentDocumentNo	PaymentDate	DocDate_ApplyZoomWindow	Invoice Paid in Days	VoucherNumber_ApplyZoomWindow	Docnumber_ApplyZoomWindow	PaymentAmount
ABC	ABC	206308	CB0191114	14/11/2019	21/10/2019	24	707794	296124	932.04
ABC	ABC	206308	CB0191114	14/11/2019	28/10/2019	17	707795	296125	1725.13
ABC	ABC	206308	CB0191114	14/11/2019	18/10/2019	27	707796	296126	170.75
ABC	ABC	206308	CB0191114	14/11/2019	23/10/2019	22	707797	296127	361.64
ABC	ABC	206308	CB0191114	14/11/2019	23/10/2019	22	707798	296128	892.98
ABC	ABC	206308	CB0191114	14/11/2019	28/10/2019	17	707799	296129	752.96
ABC	ABC	206308	CB0191114	14/11/2019	24/10/2019	21	707800	296130	229.47
ABC	ABC	206308	CB0191114	14/11/2019	30/10/2019	15	707801	296131	58.05
ABC	ABC	206308	CB0191114	14/11/2019	30/10/2019	15	707802	296132	243.24
ABC	ABC	206308	CB0191114	14/11/2019	1/11/2019	13	707803	296133	109.25
ABC	ABC	206308	CB0191114	14/11/2019	1/11/2019	13	707804	296134	1136.21
ABC	ABC	206308	CB0191114	14/11/2019	21/10/2019	24	707805	296135	112.37
CDF	CDF	206534	CB019111900	19/11/2019	24/10/2019	26	707806	296136	1456.4
CDF	CDF	206534	CB019111900	19/11/2019	18/10/2019	32	707807	296137	541.2
CDF	CDF	206534	CB019111900	19/11/2019	18/10/2019	32	707808	296138	338.8
CDF	CDF	206534	CB019111900	19/11/2019	23/10/2019	27	707809	296139	316.8
CDF	CDF	206534	CB019111900	19/11/2019	31/10/2019	19	707810	296140	2145
CDF	CDF	206534	CB019111900	19/11/2019	31/10/2019	19	707811	296141	715
CDF	CDF	206534	CB019111900	19/11/2019	28/10/2019	22	707812	296142	1485

[v-lili6-msft]

HI  @Anonymous 

You may try this way as below:

add another date table and create relationship

for Dates table, when you click one of the month in the Payment barchart, it will only return that month in dates table.

so for below visual, please use this logic to create a measure instead of your measure

New Measure=CALCULATE(SUM(Main[PaymentAmount]),FILTER('Dates 2', 'Dates 2'[Date] in VALUES(Dates[Date])))

and use dates 2 table instead of dates table in the visual too. 

Regards,

Lin