Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Unexpected filter result between two tables - calculation changes without apparent reason

I have an issue with an unexpected measure result in two different tables when I choose a value in one of the tables. 

 

Measure: 

AG Sum Total Value = Calculate (
Sum(factAccountingTransactions[amount]);
FILTER( ALL(factAccountingTransactions[dim_5]); factAccountingTransactions[dim_5] <= MAX( factAccountingTransactions[dim_5]));
factAccountingTransactions[account] >= 15130;
factAccountingTransactions[account] <= 15140
)

So basically I am summing all [amounts] for the [accounts] between 15130-15140, and this is cumulatively summed by dim_5. 
 
The table to the left is working properly. I get the correct values for each [dim_5]. However when I choose one dim_5 in the table, the right table is showing a different value, and I cannot make sense of this value. It is the same measure. 

 

 
The odd thing here is that everything works fine for 57 rows (or 57 dim_5's, to be precise)  before this issue shows up. Thereafter it shows up with uneven intervals. 
 
Anyone got a clue about what is happening with the filter here? Or possibly a way to show the fully filtered table, so I can see what rows PBI is actually summing?  
Need Help

5 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Ashish_Mathur,

       

      the expected result is that the calculation should be identical in both tables, e.g. the same result in each table. 

       

      The measure filters on max [dim_5]. The list of results in the left table is correct. It is cumulatiting results for each dim_5. 

       

      But when I choose one dim_5 in the left table - the calculation in the right table changes. I want this to show the exact same result, and I cannot understand why it doesn't? The measure is filtering on a max [dim_5]. Has the max value changed? Or has the filters changed somehow when choosing one dim_5? In situations like this I would very much like to see what Power BI is doing in the background.. 

       

      In short; 

      It is the same measure, and I would expect it to return the same result in both tables, and the filters should, in my opinion, be sufficient to handle that. 

       

      I  could share some data, but I believe I would have to share the full table to make sense of it, and unfortunately I cannot do that. 

       

       

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    7 years ago

    Hi Anonymous,

     

    I was not able to reproduce such a problem on my side. Were columns and measures in two visuals are from a same table? Please copy and paste from the left visual to test if problem exists when applying cross filter.

     

    Regards,

    Yuliana Gu

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thank you v-yulgu-msft, ,

       

      I copied the visual and it still returns the unexpected result. It could be that my dataset includes some wierd rows, but I would expect that to be handled identical in each table. All data is from the same table. 

       

      Is it possible to understand such behaviour with the given measure? 

       

      This result means that a new filter is applied or a filter is changed. 

      As far as I can understand it has to be either the max[dim_5] or the selected period (slicer), 

      but I don't see why and how... 

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi v-yulgu-msft andAshish_Mathur

         

        I've made a non-sensitive model with the required data. This also removed all other relations with other tables, and should remove that possibility of error. 

         

        Link to the file https://www.dropbox.com/s/j9l8z6pkdgrsja8/Recon%20Test%20Model.pbix?dl=0

         

        The error appears with slicer for dim_2 at "BCE" and in the table at dim_5  BCE173. 

        All previous rows in the table show correct value. 

         

        I should add that the reason for showing the same calculation twice is that I need to figure out the reason why this is not working. I will in the end show a different calculation there, but that gave the exact same problem. This is only for fault finding. 

         

        Very happy to hear your thoughts about this. 

         

        (edit; added some info)