Forum Discussion

artblakey's avatar
artblakey
Helper I
6 years ago

Visual not filtering correctly

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.

 

Need Help

16 Replies

  • HotChilli's avatar
    HotChilli
    Community Champion
    6 years ago

    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's avatar
      artblakey
      Helper I
      6 years ago

      Hi HotChilli  and Anonymous 

       

      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.

       
       
      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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

      • artblakey's avatar
        artblakey
        Helper I
        6 years ago

        Hi HotChilli  and Anonymous ,

         

        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:

         

        DateCurrencyGross VolNet VolCount
        1/04/2020AUDUSD                 143,259.93                 142,439.251
        3/04/2020AUDEUR                 111,351.11                 111,301.291
        30/04/2020AUDUSD                 201,375.78                    96,021.721

         

        Currency Table:

         

        DateCurrencyVolume
        1/04/2020AUDUSD                     23,819.65
        1/04/2020AUDUSD                     12,711.68
        1/04/2020AUDUSD                           410.34
        1/04/2020AUDUSD                     43,228.76
        1/04/2020AUDUSD                                0.36
        1/04/2020AUDUSD                           138.22
        1/04/2020AUDUSD                     10,272.25
        1/04/2020AUDUSD                     52,678.67
        1/04/2020AUDEUR                     45,059.40
        1/04/2020AUDEUR                     38,674.01
        3/04/2020AUDEUR                   107,396.14
        3/04/2020AUDEUR                        3,930.06
        30/04/2020AUDUSD                     66,612.06
        30/04/2020AUDUSD                     52,677.03
        30/04/2020AUDUSD                        4,862.65
        30/04/2020AUDUSD                        6,748.07
        30/04/2020AUDUSD                     40,170.37
        30/04/2020AUDUSD                     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.