Select row in slice to look up against

[Phil_Waite]

Hi, I have a slicebased on user input.

I have 4 columns in a table named "user assign details"as below

|Unique Id| User Id| Next Call| Counties To Include|

1 |phil@listersbrewery.com| 22/10/2019| West Sussex|

2 bob@listersbrewery.com 15/11/2019 East sussex

Expression is as below where the main table is checked for rows with columns that match User Id, Next Call and Counties to Include.

AND(CONTAINS(User Assign Details[Counties To Include],[County]),CONTAINS(User Assign Details[Next Call],[Next Call],))

I would like also like to specify which row in the slice by Unique Id to check against so that I can assign a slice to each row and therefore user.

Thanks

Phil

[WillowMobileSys]

Phil_Waite:

I would like also like to specify which row in the slice by Unique Id to check against so that I can assign a slice to each row and therefore user.

I think this needs a little more elaboration to help us understand what you are trying to achieve.

[Phil_Waite]

Hi,

There are are two tables. The main table “Full List Table” holds rows with outlets/pubs.

The second “User Assign Details” has inputs by the user in columns User Id, Next Call and Counties To Include. A slice is based on those inputs to return outlets ithat are in the “county” selected with “next call” column matching the date entered.

Currently the expression in the post above shows this as the description

ALL these statements are true:
…1: (USER ASSIGN DETAILS[Counties To Include]) contains the text value (The value of column ‘County’)
…2: (USER ASSIGN DETAILS[Next Call]) contains the text value (The value of column ‘Next Call’)

which works fine except that outlets that are in West Sussex and East Sussex and have a next call on 22/10/2019 and 15/11/2019 are returned. The expression has filtered the rows in the main Full List Table by both rows in the User Assign Details table.

I would like to specify which row in the User Assign Table to look up against to only return outlets that match criteria in the row in the table with Unique Id “1” - outlets in West Sussex with next call due on 22/10/2019.

That would result in something like below:

ALL these statements are true:
…1: (USER ASSIGN DETAILS[Counties To Include])from the row that contains Unique Id “1” contains the text value (The value of column ‘County’)
…2: (USER ASSIGN DETAILS[Next Call])from the row that contains Unique Id “1” contains the text value (The value of column ‘Next Call’)

This is a version of the “Slice Based On User Input” demo app.

I hope this helps

Many thanks

Phil

[Steve]

Try:

AND(
  IN(
    [County],
    SELECT(
      User Assign Details[Counties To Include],
      (USEREMAIL() = [User Id])
    )
  ),
  IN(
    [Next Call],
    SELECT(
      User Assign Details[Next Call],
      (USEREMAIL() = [User Id])
    )
  )
)

[Phil_Waite]

Hi thanks Steve,

Nearly there. The user Id if just for reference. I just want to use Unique Id = “1” as the row reference.

So the description would read something like this

ALL these statements are true:
…1: (The value of column ‘County’) is one of the values in the list (The list of values of column ‘Counties To Include’
…from rows of table ‘USER ASSIGN DETAILS’
…where this condition is true: Unique Id = “1”
…2: (The value of column ‘Next Call’) is one of the values in the list (The list of values of column ‘Next Call’
…from rows of table ‘USER ASSIGN DETAILS’
…where this condition is true: Unique Id = “1”

thanks

Phil

[Phil_Waite · Accepted solution]

Got It!

AND( IN( [County], SELECT( User Assign Details[Counties To Include], [Unique Id] =“1” ) ),
IN( [Next Call], SELECT( User Assign Details[Next Call], [Unique Id] =“1” ) ))

Thanks Guys

Phil

[Phil_Waite]

Phil_Waite:

AND( IN( [County], SELECT( User Assign Details[Counties To Include], [Unique Id] =“1” ) ),
IN( [Next Call], SELECT( User Assign Details[Next Call], [Unique Id] =“1” ) ))

Hi Steve,

The expression works but only with single values in the “counties to include” and “next call”. I have multiple counties and dates selected from enumlists in the input part.

Can you help me with a “CONTAINS” expression please where "User Assign Details[Counties to Include] CONTAINS [County]

The desciption of the expression (below) with IN suggests that it would do this but it is not returning a Yes/No unless there are single values on the input cells for example “Aberdeenshire” vs “Aberdeenshire, Angus”

1: (The value of column ‘County’) is one of the values in the list (The list of values of column ‘Counties To Include’
…from rows of table ‘USER ASSIGN DETAILS’
…where this condition is true: ((The value of column ‘Unique Id’) is equal to (“1”)))
…2: (The value of column ‘Next Call’) is one of the values in the list (The list of values of column ‘Next Call’
…from rows of table ‘USER ASSIGN DETAILS’
…where this condition is true: ((The value of column ‘Unique Id’) is equal to (“1”)))

Many thanks

Phil

[Steve]

In the User Assign Details table, Counties To Include is plural, implying it contains a list of values, and Next Call is singular, implying it contains only a single value. In the Full List Table table, County is singular implying it contains only a single value; likewise for Next Call.

The expression I provided should accommodate my interpretation.

How have I misinterpreted your needs?

[Phil_Waite]

Hi Steve,

No, you’ve understood pefectly. The expression work fine I realised but the Next Call element was stopping it from working. For some reason the date element doesn’t work as though somthing isn’t formatted correctly somewhere and so dates that should match aren’t.

Many thanks

Phil

[Steve]

Please provide screenshots of the Next Call column configurations from the two tables.