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]))
)

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])
)

2 Likes

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

1 Like

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 :slight_smile:

1 Like

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]))

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

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

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.

1 Like

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




image

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.

image

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.