#SOLVED I am Struggling with a Slice Row Fil...

[Henry_Scott]

#SOLVED

I am Struggling with a Slice Row Filter Condition

Scenario: I have Club Members that can renew their subscriptions Annually. I create a record for each year and have an “Expired” status on the old record and the new record status is “Valid” (for 365 days)

I want to create a Slice for a “Reminder” Report that will run daily and check per the VisitorID if they have a “Valid” Year Permit or not. I want to ONLY send the Report to the Club Members that have their latest Record (by date) as “Expired”

I am basing the Report on a Slice and I am trying to Filter the Slice to give me the appropriate data that the Report will use.

My Slice Row Filter Formula: TOP(ORDERBY(FILTER(Permit, AND([PermitIndicator] = “Year”, NOT([PermitPaymentMethod] = “Resident”), NOT([PermitStatus] = “Valid”), SELECT(Permit[PermitDate],[VisitorID]=[_THISROW].[VisitorID]), [PermitDate]) ), [PermitDate], TRUE),1)

Gives me error: “Invalid structure: subexpressions must be Yes/No conditions”

Any suggestions?

[Aleksi_Alkio]

Why don’t you just slice the data with the Expired value?

[Henry_Scott]

@Aleksi_Alkio because there could already be a “Valid” one created and I do not want to send a reminder Email if that is the case. So I want to make sure the list of records that I have for a Year Visitor selects the latest one and then ONLY send an Email if that Status is “Expired”. The latest one could be “Valid” and then no email must be sent. Scenario is that for the past 3 years this Visitor could have renewed his Year Membership and I keep the history e.g. ~ Current 365 Days - “Valid” ~ Previous 365 Days - “Expired” ~ Previous Previous 365 Days - “Expired” etc.

[Henry_Scott]

@Aleksi_Alkio ok I removed the SELECT by rewriting the Formula in the VC of the Visitor Table to read…=MAXROW(Permit, PermitDate, AND([VisitorID] = [_THISROW].[VisitorID], [PermitIndicator] = “Year”, NOT([PermitPaymentMethod] = “Resident”), [PermitStatus] = “Expired”)) What will this return?? As I understand correctly how MAXROW works based on the Help documentation is that it returns the Keys of the Records of the Permit Table that adheres to the MAXROW Filter??

How do I use that to create a Slice

in the Visitor Table? to run my Report from?

[Henry_Scott]

@Aleksi_Alkio

Do you have some more feedback.

The previous Post’s formula “did not work” as I could not use the Keys to Ref back to the Permit Table to get the [PermitStatus] =MAXROW(Permit, PermitDate, AND([VisitorID] = [_THISROW].[VisitorID], [PermitIndicator] = “Year”, NOT([PermitPaymentMethod] = “Resident”), [PermitStatus] = “Expired”))

I am stuck and do not know how can I get the [PermitStatus] Value from the Permit Table for that Latest Record Sorted by [PermitDate] into the Virtual Column on the Visitor Table

[Henry_Scott]

@Aleksi_Alkio - OK SOLVED

Virtual Column Formula: =ANY(SELECT(Permit[PermitStatus], [PermitID] = MAXROW(Permit, PermitDate, AND([VisitorID] = [_THISROW].[VisitorID], [PermitIndicator] = “Year”, NOT([PermitPaymentMethod] = “Resident”))),TRUE))

Slice Formula (to run Daily Report from): [VisitorYearPermitStatus] = “Expired”

[Aleksi_Alkio]

One way is if you create a virtual column like MAXROW(Permit, PermitDate, AND([VisitorID] = [_THISROW].[VisitorID], [PermitIndicator] = “Year”, NOT([PermitPaymentMethod] = “Resident”))) then you can read the value with a Deref like… [LastRowColumnName].[PermitStatus]=“Expired”

[Henry_Scott]

@Aleksi_Alkio the Deref is then used in the Slice Condition formula?

[Aleksi_Alkio]

Correct

[Henry_Scott]

@Aleksi_Alkio ok thanks will try it that way

[Aleksi_Alkio]

I’m assuming the user has the same ID for all his/her records, correct?

[Henry_Scott]

@Aleksi_Alkio The VisitorID is the same in the Permit Table and then there is a PermitID that is unique for each Permit Record in the Permit Table. There is a Ref to the Visitor Table using VisitorID

[Aleksi_Alkio]

Do you want to trigger the report from the parent or child table?

[Henry_Scott]

@Aleksi_Alkio from the Child Table which is the Permit Table. The Slice is built in the Permit Table

[Aleksi_Alkio]

I was thinking this… add a virtual column with the MAXROW expression and search the latest status for that VisitorID and then you can slice the data directly from the Visitor table. Then the slice filter can be like [LAST_STATUS]=“Expired”

[Henry_Scott]

@Aleksi_Alkio hmm ok. Sounds like a cleaner solution. Will try that.

[Henry_Scott]

@Aleksi_Alkio I tried the formula below in a VC in the Visitor Table but get an error. Thinking with this formula is that I use PermitDate to get the latest Permit for the VisitorID and then Filter it with the SELECT to return the Permit[PermitStatus]

Formula: =MAXROW(Permit, PermitDate, SELECT(Permit[PermitStatus], [VisitorID] = [_THISROW].[VisitorID], AND([PermitIndicator] = “Year”, NOT([PermitPaymentMethod] = “Resident”))))

Error: Function ‘MAXROW’ should have at least two parameters: a table name, a column name, and an optional filter expression

[Henry_Scott]

@Aleksi_Alkio a quick elimination test indicates to the fact that MAXROW does not like the SELECT Statement in the 3rd but optional Parameter. Can you suggest a change?