Visual not filtering correctly

[artblakey]

Hi friends,

Hope someone can assist me please.

I'm currently using three visuals in PowerBI:

1. A bar chart for different currencies and corresponding volume (eg. AUD, EUR. GBP etc.)

2. A card with a gross volume amount - Measure: Gross = CALCULATE(SUM(Bulking[Gross]),Bulking[Count] = 1)+0

3. A card with a net volume amount - Measure: Net = CALCULATE(SUM(Bulking[Net]),Bulking[Count] = 1)+0

I also have two Tables:

- A "Currency" table with the below columns (for the bar chart):

A: Date

B: Currencies

C: Volume Amount

- A "Bulking" table with the below columns (for the Cards):

A: Date

B: Currency (A currency is listed here if there is a netting opportunity, otherwise the cell is blank)

C: Gross Volume Amount (currency gross amount is listed here if there is a netting opportunity, otherwise the cell is blank)

😧 Net Volume Amount (currency netamount is listed here if there is a netting opportunity, otherwise the cell is blank)

E: Count (number 1 if there is a netting opportunity, otherwise the cell is blank)

I have a Slicer that filters by Year and Month.  The Slicer works as intended for the page, meaning that when I select a month and year, if filters the Currency bar chart and the two Cards correctly as expected.

The problem happens after I select a year/month, and then try to filter further by selecting a specific Currency from the bar chart.  I would expect the cards to filter down to the next level as well and give me the gross/net amounts for that specific Currency, but instead they remain unchanged.

What am I missing here?  I tried to change the relationships but was unsucessful, but truthfully I'm not really sure what the relationships should be.  Is there a dax option I could try out instead?

Thanks in advance.

[artblakey]

[HotChilli]

As suspected, the direction of relationships in the model doesn't allow the filtering.

You can read up on this here https://radacad.com/what-is-the-direction-of-relationship-in-power-bi 

[artblakey]

Hi @HotChilli  and @v-yiruan-msft ,

Thanks for the information.  I read the article and changed the cross filter direction between the Date and Currency table from Single to Both.  This did change the values in the Cards, but not really as expected.  I could not figure out how to share the file via OneDrive, but here is the sample data of the tables:

Bulking Table:

Date	Currency	Gross Vol	Net Vol	Count
1/04/2020	AUDUSD	143,259.93	142,439.25	1
3/04/2020	AUDEUR	111,351.11	111,301.29	1
30/04/2020	AUDUSD	201,375.78	96,021.72	1

Currency Table:

Date	Currency	Volume
1/04/2020	AUDUSD	23,819.65
1/04/2020	AUDUSD	12,711.68
1/04/2020	AUDUSD	410.34
1/04/2020	AUDUSD	43,228.76
1/04/2020	AUDUSD	0.36
1/04/2020	AUDUSD	138.22
1/04/2020	AUDUSD	10,272.25
1/04/2020	AUDUSD	52,678.67
1/04/2020	AUDEUR	45,059.40
1/04/2020	AUDEUR	38,674.01
3/04/2020	AUDEUR	107,396.14
3/04/2020	AUDEUR	3,930.06
30/04/2020	AUDUSD	66,612.06
30/04/2020	AUDUSD	52,677.03
30/04/2020	AUDUSD	4,862.65
30/04/2020	AUDUSD	6,748.07
30/04/2020	AUDUSD	40,170.37
30/04/2020	AUDUSD	29,950.85

When I click on AUDUSD in the bar chart, I get the correct result:

But when I click on AUDEUR, I get incorrect figures for some reason:

What I am expecting for AUDEUR is:

Gross: 111,351.11

Net: 111,301.29

Am I doing something wrong with the relationships?

Thanks in advance.

[v-yiruan-msft]

Hi @artblakey ,

Please create the relationship between Bulking and Currency table based on field "Currency".

Best Regards

Rena

[artblakey]

Hi @v-yiruan-msft 

I created the relationship as suggested, but I'm still getting the same result.  I also have a Dates table (based on field "Date") so I'm not sure if this is affecting the result?  This is how the relationships are currently set up:

[v-yiruan-msft]

Hi @artblakey ,

Please active the relationship which be created between Bulking and Currency table based on Currency field and de-active or remove the left 2 relationships.

Best Regards

Rena

[artblakey]

Hi @v-yiruan-msft ,

Thanks for the suggestion.  The problem is that I have other visualizations that use the relationship between the Currency table and Dates table (based on Date field).  If I remove this relationship then it throws off the other visualizations which run off a Year/Month Slicer.

Is there a way to run both of these relationships at the same time (Dates/Currency and Bulking/Currency)?

Thanks for your help on this.

[v-yiruan-msft]

Hi @artblakey ,

Could you please share your sample pbix file with me if it is convenient? Later I will check if there is any other method/workaround to achieve it base on your report file. You can upload it to OneDrive for Business and share the link with me. Thank you.

Best Regards

Rena

[artblakey]

Hi @v-yiruan-msft 

Here is the link for the pbix file from OneDrive.  Please let me know if you have any questions.

Thanks.

https://1drv.ms/u/s!ArgKVdagPZNkd9ih385H3_mX5X4?e=Xnp7Yf

[v-yiruan-msft]

Hi @artblakey ,

Sorry for late response. I made some changes on your provided report file to update the formula of measure Gross and Net as below:

Gross = CALCULATE(SUM(Bulking[Gross Vol]),FILTER('Bulking',Bulking[Count] = 1&&'Bulking'[Currency]=SELECTEDVALUE('Currency'[Currency],"AUDEUR")))+0

Net = CALCULATE(SUM(Bulking[Net Vol]),FILTER('Bulking',Bulking[Count] = 1&&'Bulking'[Currency]=SELECTEDVALUE('Currency'[Currency],"AUDEUR")))+0

You can get the updated report file from this link.

Best Regards

Rena

[artblakey]

Hi @v-yiruan-msft 

Thanks for your reply and for taking the time to help me out.

I looked at the changes but it's not quite giving me the desired results.  Apologies as I probably wasn't clear on what I was expecting as the end result.  Here is a summary of what I am expecting to see from this report:

April:

APRIL	Total Volume (from Currency table)	Gross (from Bulking table)	Net (from Bulking table)	% Difference
ALL SELECTED	539,340.57	455,986.82	349,762.26	23.3%
AUDUSD	344,280.96	344,635.71	238,460.97	30.8%
AUDEUR	195,059.61	111,351.11	111,301.29	0.0%

May:

MAY	Total Volume (from Currency table)	Gross (from Bulking table)	Net (from Bulking table)	% Difference
ALL SELECTED	1,078,681.14	911,973.64	349,762.26	61.6%
AUDUSD	688,561.92	689,271.42	238,460.97	65.4%
AUDEUR	390,119.22	222,702.22	111,301.29	50.0%

It appears with the changes you made, it does work properly when you select and click on either currency, but it is not working correctly when all currencies are selected.  For example, for April, when all currencies are selected (no currency filter), I'm expecting a Gross volume of $455,986.82 and Net volume of $349,762.26, with a Percentage Difference of 23.3%.  The screenshot below is showing the current results which are incorrect.

I also wanted to mention that this report will be generated on a regular basis, and it will have multiple currencies in future, not just the two shown on this file, but the calculations will always be the same.

Please let me know if I can provide any other information.

Thanks.

[artblakey]

Hi,

Is anyone able to help me with this please?

I appreciate all the help from everone so far and it seems like It's getting close, but just need a little more help to get over the line.

Thank you.

[v-yiruan-msft]

Hi @artblakey ,

Could you please provide some sample data in Currency and Bulking table? Is there any relationship created between these two tables? Where the field applied to the slicer come from? From a date table or Currency table?

Best Regards

Rena

[HotChilli]

We will need to see the relationships and are there any other tables?  

The date slicer filters both tables so that would imply a date table.  Is that right?

It's possible that the nature and direction of the relationships in the model don't allow the Currency table to filter the Bulking table.

[artblakey]

Hi @HotChilli  and @v-yiruan-msft 

Thanks for your response.  I re-created the data on a new report, but I cannot figure out how to upload the PBIX file, or if I'm even allowed to upload the file.  Please let me know if I can do this to make things easier.  For now I will attach images:

Yes, I am using a date table and the relationships are as below.  I have also attached the Desktop image with the bar chart and cards.  As I mentioned, the cards work well with a Slicer panel, but when I click on one of the individual currencies, the cards should change and they do not.

Please let me know if I can provide any other information.

Thanks.

[v-yiruan-msft]

Hi @artblakey ,

You can upload your pbix file to OneDrive for Business and share the link with us. Or you can provide some sample data in these three tables. Thank you.

Best Regards

Rena