Dashboard Filtered View not responding to Start/End Date Filter

Hi everyone, I’m having trouble with the right expression on a slice to reflect the data from the parameters selected on an interactive Dashboard. In the image below “Filters” is based on the Parameters table and Quotes is based on a slice with this expression. The top 3 expressions respond perfectly but when I add Start/End time it displays the blank screen. What’s the best expression for the Start/End Dates?

Any help would be outstanding! Thanks

AND(
CONTAINS([Depot],ANY(Parameters[Depot])),
CONTAINS([Assigned to],ANY(Parameters[Assigned to])),
CONTAINS([Quote Stage],ANY(Parameters[Quote Stage])),
CONTAINS([Quote Date],[Quote Date]>ANY(Parameters[Start Date])),
CONTAINS([Quote Date],[Quote Date]<ANY(Parameters[End Date]))
)

Solved Solved
0 34 854
1 ACCEPTED SOLUTION

Does the following expression help?

AND(
CONTAINS([Depot],ANY(Parameters[Depot])),
CONTAINS([Assigned to],ANY(Parameters[Assigned to])),
CONTAINS([Quote Stage],ANY(Parameters[Quote Stage])),

[Quote Date]>ANY(Parameters[Start Date]),

[Quote Date]<ANY(Parameters[End Date])
)

View solution in original post

34 REPLIES 34

Does the following expression help?

AND(
CONTAINS([Depot],ANY(Parameters[Depot])),
CONTAINS([Assigned to],ANY(Parameters[Assigned to])),
CONTAINS([Quote Stage],ANY(Parameters[Quote Stage])),

[Quote Date]>ANY(Parameters[Start Date]),

[Quote Date]<ANY(Parameters[End Date])
)

That was it! A million thanks Suvrutt for unblocking my brain

Hello, I am wondering how did you make the filter option in the dashboard?? When i look at your picture the filter view on the left. thank you

Hello, this was created with a table I called Parameters set solely for the purpose of capturing the user’s filtering options and contains one row per user email. You’d create a Quick Edit Detail View to display the Parameter options on the left.

For the resulting data, you’ll create a slice and more than likely a table view based on the slice. The slice will contain the expression above to reference the data from the Parameters table.

AND(
CONTAINS([Depot],ANY(Parameters[Depot])),
CONTAINS([Assigned to],ANY(Parameters[Assigned to])),
CONTAINS([Quote Stage],ANY(Parameters[Quote Stage])),
CONTAINS([Quote Date],[Quote Date]>ANY(Parameters[Start Date])),
CONTAINS([Quote Date],[Quote Date]<ANY(Parameters[End Date]))
)

Hope this helps to get things started for you

the formula is not working. At all choices i only get the unique ID from my main sales table.

AND(
CONTAINS([Client],ANY(Filter[Client])),
CONTAINS([Attribué à:],ANY(Filter[Attribué à:])),
CONTAINS([Type],ANY(Filter[Type])),
[Date d’installation]>ANY(Filter[Date Debut]),
[Date d’installation]<ANY(Filter[Date Fin])
)

ALL these statements are true:
…1: (The value of column ‘Client’) contains the text value (One randomly chosen value from this list (
…FILTER[Client]))
…2: (The value of column ‘Attribué à:’) contains the text value (One randomly chosen value from this list (
…FILTER[Attribué à:]))
…3: (The value of column ‘Type’) contains the text value (One randomly chosen value from this list (
…FILTER[Type]))
…4: (The value of column ‘Date d’installation’) is greater than (One randomly chosen value from this list (
…FILTER[Date Debut]))
…5: (The value of column ‘Date d’installation’) is less than (One randomly chosen value from this list (
…FILTER[Date Fin]))

even when nothing is selected I do not see anything in the sliced table. It is always blank

i am not sure if i tagged you in my last post

ok so i spend a lot of time on this today and i figured it out. It is working but if there is no data in the field i do not get any results. meaning if filter is all empty instead of seeing all i have no data.

Now i have a problem, one of my filter parameters is an enum column and i get this error:

Table slice Filter transactions uses column ‘Related Filters By Type’ that does not exist in root table Transactions. Removing this column.

It is working for clients since it is a direct reference to the transaction table but since my “type” column is an enum it is not working… anyway to get around this??

It is enum because based on the input there is a second question appearing and i can not undo this.

@Steve Can you help me?

Please post a new topic and describe your problem in detail there, with appropriate screenshots. I find it confusing trying to differentiate your problem from the original poster of this topic,

Apologies Andrian as I had a birthday weekend and was out for the weekend. Here are a few notes:

“ok so i spend a lot of time on this today and i figured it out. It is working but if there is no data in the field i do not get any results. meaning if filter is all empty instead of seeing all i have no data.”

This is correct - I usually set a few defaults in the filter options for visual appeal

“Table slice Filter transactions uses column ‘Related Filters By Type’ that does not exist in root table Transactions. Removing this column.”

It sounds like “Related Filters by Type” does not exist in the Transactions" table? Can you send a screenshot of the slice filter with complete expression as well as a screenshot of the fields in your “parameters” table? In the meantime, please double check the slice/table associated with 'Related Filters By Type’ to ensure the enum field exists in the associated slice/table.

I think i got everything you asked for. The enum is indeed working maybe i didn’t associated it the right way…




3X_1_1_119749f81a6bf507931664bf9f62a48fafbe8d73.png

look if i associete the type colum in my filter table to the type tab i get this in my filter view



Let’s confirm the data being stored in the Transactions table for Type. Is it the key for Type? Let’s ensure the following represents the data being stored in the table.

3X_d_d_dd4801d784cd9f3bc9905d43f8a80ec6ab723158.png

I am not sure what you mean. In my Type table the key is the row number and in my transaction table I have a uniqueid as key.

this is how my type sheet was build:


There is a total of 258 rows and i might add some more.

Can you show how Type is stored in the Transactions table?



Oh I see, there are no records yet in the transactions table. You might want to input a few records so you can test the filtering.

For Types: I recommend a unique value (key) for each record of the Types table - Row Number can unfortunately cause issues if a record is deleted as row references the physical position of the record and does not reference the specific record.

Once you have a unique value for each unique record in Types then for the Enum you’ll want to use Types[Unique Value]. We need to pull the data referenced in the transactions so when a transaction is entered, the key of Types is stored in the transactions table.

I hope this makes sense.

unique values can i use 1 to 258? or it has to be complicated?

1 thru 258 is fine. When someone enters a new Type, they’ll just need to know 259 is the next number in the sequence. You can always use UNIQUEID() and you won’t have to worry about inputting a unique value.

the types were created directly in the google sheet they will only change if we add a new code or if we remove one and that is so rare that i do it manually.

is there a more efficient way to build an enum list? In this app you have to select the type (enum) after the service (enum) and then the billable codes (enumlist). Obviously based on the first choises the others change.

where exactly am i supposed to put the formula Type[Unique Value]?

What is the name of the new unique value field you created for Types? This new field should be marked as the key for the Types table. You’ll want to replace the “TYPE” in this formula with the following:

Type[New Field Name]

i want to say thank you!! for not letting me down i really appreciate your time!!

here is the new column:

You’re welcome!

Ok use: Type[IDType] and please make sure the field is set to the table’s key.

Once you’re done with that, you’ll want to key in a few test records in your Transactions table so we can see if it worked.

but where exactly do i need to put this formula?
in the filter table or in the type table or transaction table?

You’ll want to replace the “TYPE” in this formula with the following:

Type[IDType]

Type[New Field Name]

but if i do that in my transaction table under the type i no longer get my text option i only see the uniqueid from the type table the numbers 1 to 258

In the Types table set the text you’d like to see to “Label”

I think i am about to cry…



No please - there’s no crying in AppSheet developing

Did it work?!!

If no, let’s jump online. We can do a join.me or something

Firs of all i want to say a big thank you to @Daisy_Ramirez. Without your idea we would of never got the solution that is so simple.

Solution: when you want to reference to a filter from an enum list create a new tab in your sheet with the filter metrics from your enum list (for me was the uniques from the type column) and link the filter to that new tab

3X_4_0_4062774d4c78654effd29b46ec31fc5cc2a2db0a.png
3X_e_b_eba0f693524047af6205d59bc4c9554f6797a4d2.png

Top Labels in this Space