Row Filter Condition for Slice

[Andre_van_Aarde]

Hi Guys

I have two tables for counting stock, STOCK COUNT and STOCK COUNT DETAILS. The stock count table creates a new entry every time out staff perform a stock count at a customer, the details table is linked to the stock count table but contains the specific count QTY for a given product. It is "Part Of" the STOCK COUNT table.

Given that some stores may have multiple stock counts taken, I want to create a Slice of the STOCK COUNT DETAILS  table that ONLY shows the results of the latest stock count taken for all stores -

** some stores may only have one count while some may have several, in which case I would want to show the stock count for the stores that only have one count, and then only the most recent count for the stores that have multiple.

My columns are as follows:

[ID]

[Ref ID] - reference ID to the STOCK COUNT table

[Brand] - brand counted

[QTY] - QTY of brand counted

[Count Date] - timestamp date for the count

[Company] - customer where the count was taken

I just can't wrap my head around the required logic here...

[Suvrutt_Gurjar]

Is the [Company] column same as store you have mentioned in the description?

If so , please try following expression for the slice of the STOCK COUNT DETAILS  table.

[ID] = MAXROW("STOCK COUNT DETAILS", "Count Date", AND([Brand]=[_THISROW].[Brand], [Company]=[_THISROW].[Company]))

[Andre_van_Aarde]

@Suvrutt_Gurjar yes so there would be multiple lines for each QTY [QTY] of product [Brand] counted at a given company on the same day [Date].

In some instances, there would be multiple days [Date] for the given company, in which instance I would only want to show the most recent count for the company.

So only show lines for counts taken at a company where there is only one date on which the count has taken place OR only show the lines from the latest count for that company.

[Andre_van_Aarde]

@Suvrutt_Gurjar I tried your formula but it did not seem to work as I intended. See below an image of the data table:

I would NOT want to show the lines highlighted in RED as you will see at the bottom of the image in BRICHT GREEN there is a more recent count for the same company (342)

I DO want to show the lines highlighted in GREEN as there are no more recent stock counts for these companies, there is only one count for each of the companies.

I DO want to show the lines highlighted in BRIGHT GREEN as this is the most recent count for company 342

[Suvrutt_Gurjar]

Thank you. Please try the suggested expression for the slice filter.

[Andre_van_Aarde]

Hi @Suvrutt_Gurjar not sure if you saw my reply:

@Suvrutt_Gurjar I tried your formula but it did not seem to work as I intended. See below an image of the data table:

I would NOT want to show the lines highlighted in RED as you will see at the bottom of the image in BRICHT GREEN there is a more recent count for the same company (342)

I DO want to show the lines highlighted in GREEN as there are no more recent stock counts for these companies, there is only one count for each of the companies.

I DO want to show the lines highlighted in BRIGHT GREEN as this is the most recent count for company 342

[Suvrutt_Gurjar]

Thank you. Could you update what result you are getting? The shared expression looks good to me. However you do not have any column by the name [Counted Date]. Hope you have replaced it by [TimeStamp]

[ID] = MAXROW("STOCK COUNT DETAILS", "Timestamp", AND([Brand]=[_THISROW].[Brand], [Company]=[_THISROW].[Company]))

[Andre_van_Aarde]

Hi @Suvrutt_Gurjar 

Thanks again for your assistance here, but I'm not getting the correct result still...

I have confirmed the column names are correct in the formula, apologies for my error here. See below the image showing what I require on the left vs the result your formula generates on the right. You will see your formula includes the first two entries for some reason despite there being a more recent stock count for this customer 342. But confusingly, it does not include all of the entries from the first count, it only includes the first two and then excludes the next four...

Perhaps an alternative / simpler filter to try would be to filter the entries on the parent table [Stock Count] table to which these data entries belong? This table would be the following:

For this table you will see the single entry for company 342 dated 15/03/2022 would need to be excluded as there is a more recent entry dated 17/03/2022 for the same company. All of the other entries must still be included as there are no other / newer entries for these companies.

^^ perhaps getting a row filter for this [Stock Count] table would be simpler and then use this to generate a row filter for the [Stock Count Details] table?

[Suvrutt_Gurjar]

As per my understanding the result is correct. The first two rows for company 342 are for brands cb_42 and CB_43 on 15/03/22, those brands have not repeated afterwards for that company (342) on 17/03/2022. So they ( the first 2 records) are appearing as their latest counting was on 15/03/2022.

So if the latest entry for each brand for each company needs to be captured , then the filter condition is correct.

[Andre_van_Aarde]

Ok, I understand. Our system / user input assumed that if there are no values for a brand then it is "zero" so we are not counting zero. Is there any way to work this logic into the formula you have given?

[Suvrutt_Gurjar · Accepted solution]

Such a specific requirement over multi row expressions can be sync time expensive. Please try as below. 

1. Please create a VC called say [CompanyMaxDate] in the table "STOCK COUNT DETAILS" with an expression something like MAXROW("STOCK COUNT DETAILS", "Timestamp", [Company]=[_THISROW].[Company])

2. Then your slice expression can be 

[ID] = MAXROW("STOCK COUNT DETAILS", "Timestamp", AND([TimeStamp]=[CompanyMaxDate].[TimeStamp], [Company]=[_THISROW].[Company]))