cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Nevos
Frequent Visitor

Inconsistant filter results

I'm getting some really odd behaviour when filtering tables using a measure.  I have two tables (one a copy of the other) and when i remove two collumns from the second table my results change. background I'm using a hybrid push dataset for testing.

Capture.png

 

I have just 5 fields with some data being sent via a PS script.  Nothing complicated. I've also two measures.  The first calculates the latest row of data for each agent.

Capture4.PNG

The second is used as a filter so that only the last row is displayed in each table.

Capture3.PNG

The odd thing though, is that i get two different results depending on which collumns are displayed in the table. The first table displays all raw data. 

Capture2.PNG

The second table has the filter applied so that only the most recent row for each agent is displayed. This is eactly the data i'm after. The third table is a copy and paste of the second with two columns removed. What i don't understand is why i get different data results.  It's as though the filter is no longer applied

6 REPLIES 6
v-alq-msft
Community Support
Community Support

Hi, @Nevos 

 

There are two ways. One way is to make the fields in the visual 'Don't summarize'. Another way is to create a new measure as follows.

 

TotalInbound = CALCULATE(SUM(RealTimeData[InboundCalls]),FILTER('RealTimeData','RealTimeData'[LastUpdated] = [LatestDate]))

 

 

Result:

d1.png

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Nevos
Frequent Visitor

Hi Allan and thanks for the response.  Yes removing the field to 'Don't summerise' gives the desired result but it doesn't explain why?  Both tables have the field set to SUM and are referencing the same data, measures and fields.  So why two different results?

 

I've tried your suggestion of the new measure but it doesn't give any data at all 😞 

tex628
Community Champion
Community Champion

Is the 3rd field, "Internal calls" also set to sum? 


Connect on LinkedIn
Nevos
Frequent Visitor

No, if i change that to SUM that changes the values in ALL other columns to give the SUM of all data and not just the most recent row.

 

i don't understand that either 

tex628
Community Champion
Community Champion

Alright. The reason that your two tables are behabving differently is because "InternalCalls" is not set to be summarized.

I will try to explain why. 

When all of the columns are summarized there are no specific dates remaining to be used in the comparison of isLatest. You're expecting it to get the individual dates of the rows but since everything is summarized there are no rows to get the date from.


Essentailly what this means is that your two measures ends up doing the same thing. In your first measure you use the ALLEXCEPT to ignore the individual rows and find the max date related to the specific agent. 

In the second measure you end up doing the exact same thing with the max statement since it's evaluated over all of all the rows for each agent. 

My advice would be to use a calulated column in the dataset to determine the most recent rows beforehand and then use that as the filter instead. 

I hope this was some help! 

Br,
J



Connect on LinkedIn
Nevos
Frequent Visitor

Thanks J, I appreciate the input.  I was all set to give your comments a big thumbs up until....

 

At the same time as turning to the forums for help i also asked a colleague to take a look.  He aproached it in the same way as me though his measure for comparing the current row against the most recent data entry was slightly cleaning (see below).

 

IsLatest = If(MAX(RealTimeData[DateTime]) = RealTimeData[LatestDate], 1, 0)
 
Now the frustrating thing is that his table worked perfectly. It filtered all values correctly and also summerized each column based on the results and not the full under laying data.  It is necessary to have the LastUpdated column visable on the table though.  I went back and applied the same measures to my data but i ended up with values missing.  So i then created a new report with a fresh set of data and everything kicked into life (as below).
final.PNG
 
 
So there seems to have been two things wrong.  First there was something about my filter/measure that didn't like a True/False result and secondly something is off about my test data.
 
I'll continue to disect the result to confirm conclusively what the full issue is and then update the post again.
 
thanks all.

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors