Dynamic filters in dashboart ...Histogram that omits blank values ​​from an enum column

[Msant77s]

Hello dears, I am contacting you to ask you about an inconvenience I am having when developing my application! It turns out that one of the tables that I am trying to represent in a dashboar has columns of type enum with validations to pass or not to the next column (This is because it is an inspection form and in many cases depending on the response it passes or not the next). When developing the filter and the views associated with that filter, I can't find a way for the charts not to count the blank cells...example: My chart is left with YES, NO, BLANK. I detail the condition of the filter... I don't know if something can be added to this expression to help me with the columns that have this inconvenience.

AND(IF(ISBLANK(ANY(DashboardfilterPro [INDUSTRY])), true, ANY(DashboardfilterPro [INDUSTRY])=[INDUSTRY]),

IF(ISBLANK(ANY(DashboarfilterPro [Overall Machine or Equipment Risk Level])), true, ANY(DashboarfilterPro [Overall Machine or Equipment Risk Level])=[Overall Machine or Equipment Risk Level]),

IF(ISBLANK(ANY(DashboarfilterPro [Query Start])), true, ANY(DashboarfilterPro [Query Start])<=[Date]),

IF(ISBLANK(ANY(DashboarfilterPro [End of Query])), true, ANY(DashboarfilterPro [End of Query]) >=[Date]))

On other individual slices for each column I have tried this:

ISNOTBLANK([Type of emergency stop device:])

When I do it for each individual column it works... the issue is then being able to manage my dashboar with a single filter!!

From already thank you very much for your time!!

[Suvrutt_Gurjar]

Could you please add some more details to your requirement?

Do you have issues when filtering slice records when all the selections in the corresponding dashboard filter are blank or are you getting issues when all the column values used in the filter are blank in the table on which you are applying filter?

would you please mention those column types in both the sides? filtering table and filtered table? I would assume they are all enums in the filtering table.

[Msant77s]

Yes, there goes the detail!

I have this general table called "Protections". It has enum columns with validations as explained above. Each user depending on the condition evaluated may not go to the next column (this is why there are empty fields in different enum columns)

I have created another table to be able to make dynamic filters in my dashboard of the different columns in the form of graphs.

Then I have created a slice with reference to the main table of protections ("Protections") and with a filter condition in reference to the table created for filter ("DashboarfilterPro").

To then finally be able to include it in the Dashboard with this slice.

But as you can see, the charts take the blank values ​​of the enum columns, when that is not desirable in the application.

I hope you can understand my query a little better, sorry if it is not possible. Thanks for your time

[Suvrutt_Gurjar]

Thank you for all the details. I think there may still be more details requried. But for now, based on the shared details, could you try with the following slice filter? Have not tested the below even for syntax errors.

If it does not work, will request more details from you tomorrow.

AND(IFS(ISNOTBLANK(ANY(DashboardfilterPro [INDUSTRY])), ANY(DashboardfilterPro [INDUSTRY])=[INDUSTRY]),

IFS(ISNOTBLANK(ANY(DashboarfilterPro [Overall Machine or Equipment Risk Level])), ANY(DashboarfilterPro [Overall Machine or Equipment Risk Level])=[Overall Machine or Equipment Risk Level]),

IFS(ISNOTBLANK(ANY(DashboarfilterPro [Query Start])), ANY(DashboarfilterPro [Query Start])<=[Date]),

IFS(ISNOTBLANK(ANY(DashboarfilterPro [End of Query])),ANY(DashboarfilterPro [End of Query]) >=[Date]))

[Msant77s]

I have tried both ways and the result is that with isblank it continues to show me blank values ​​and with isnotblank it does not show me data directly.

ISBLANK (Unfiltered)

with filter

ISBLANK (with filter)

ISNOTBLANK

In addition to this query, another thing that is happening to me is that when doing a filter, there is only one count value left in the graphs, it removes the established color format 😕 (image 2)

[Suvrutt_Gurjar]

Thank you for more details. I think to better understand your requirement, you may share the data pattern of your table , "Protections". 

You have mentioned the below

Does the user always fill in those enums in the specific order- Enum1, Enum2, .....Enum6 and if Enum2 is No, rest of the enums Enum3, Enum 4, Enum5 and Enum6 are not evaluated?

If not so , I believe it will not be possible to manage all the enums with single filter because there can be many combinations of Yes and No and Blank in a single row that no expression can filter individually.

Even if they fill the Enum's in specific order, the filter expression will be very long, needing to check each Enum status,

[Msant77s]

Hello dear, thank you very much for your answer! it is as you say. I share a view of the results.

So there is no way to fix that? Who could I contact so that they can help me with an alternative?

[Suvrutt_Gurjar]

Thank you. May we know how many those enum columns are there? And can you mention the names of those Si/No/Blank enum columns.  I see there are around 9 such columns and all are either filled up with Si/No or all are blank in some rows.

One good thing is they are all blank together. If  so, you could create a VC called [EnumConcatenate] with an expression something like 

LIST([Enum 1] , [Enum 2] , [Enum 3]........,  [Enum 9])

AND(IF(ISBLANK(ANY(DashboardfilterPro [INDUSTRY])), true, ANY(DashboardfilterPro [INDUSTRY])=[INDUSTRY]),

IF(ISBLANK(ANY(DashboarfilterPro [Overall Machine or Equipment Risk Level])), true, ANY(DashboarfilterPro [Overall Machine or Equipment Risk Level])=[Overall Machine or Equipment Risk Level]),

IF(ISBLANK(ANY(DashboarfilterPro [Query Start])), true, ANY(DashboarfilterPro [Query Start])<=[Date]),

IF(ISBLANK(ANY(DashboarfilterPro [End of Query])), true, ANY(DashboarfilterPro [End of Query]) >=[Date]),

ISNOTBLANK([EnumConcatenate])

)

Please again note, they need to be all blank in some rows that need to be eliminated.

[Msant77s]

That's right, once the user chooses "NO" in the first validation enum, then the fields are left blank for those subsequent columns (enum 2, enum3...enumN).

I have tried the expression, I take this expression well but it does not do the filter, the graphs remain the same showing the blank values

Expression:

AND(IF(ISBLANK(ANY(DashboarfilterPro [SECTOR])), true, ANY(DashboarfilterPro [SECTOR])=[SECTOR]),

IF(ISBLANK(ANY(DashboarfilterPro [Nivel de Riesgo general de máquina o equipo])), true, ANY(DashboarfilterPro [Nivel de Riesgo general de máquina o equipo])=[Nivel de Riesgo general de máquina o equipo]),

IF(ISBLANK(ANY(DashboarfilterPro [Inicio de Consulta])), true, ANY(DashboarfilterPro [Inicio de Consulta])<=[Date]),

IF(ISBLANK(ANY(DashboarfilterPro [Fin de consulta])), true, ANY(DashboarfilterPro [Fin de consulta]) >=[Date]),

ISNOTBLANK(LIST([¿Los dispositivos de paro de emergencia son fáciles de ubicar e identificar por los usuarios?] ,[Tipo de dispositivo de paro de emergencia:] ,[¿Se encuentra señalizado y es de fácil acceso?],[¿Se encuentra libre de polvo o material que impida su funcionamiento?], [¿Se encuentra al alcance de cualquier usuario?],[¿Sus componentes se encuentran en buen estado (sin daños, fisuras, roto)?],[¿Sus conexiones estan en buen estado (sin daños, fisuras, roto)?]))

)

[Suvrutt_Gurjar]

Please try 

AND(IF(ISBLANK(ANY(DashboarfilterPro [SECTOR])), true, ANY(DashboarfilterPro [SECTOR])=[SECTOR]),

IF(ISBLANK(ANY(DashboarfilterPro [Nivel de Riesgo general de máquina o equipo])), true, ANY(DashboarfilterPro [Nivel de Riesgo general de máquina o equipo])=[Nivel de Riesgo general de máquina o equipo]),

IF(ISBLANK(ANY(DashboarfilterPro [Inicio de Consulta])), true, ANY(DashboarfilterPro [Inicio de Consulta])<=[Date]),

IF(ISBLANK(ANY(DashboarfilterPro [Fin de consulta])), true, ANY(DashboarfilterPro [Fin de consulta]) >=[Date]),

(COUNT(LIST([¿Los dispositivos de paro de emergencia son fáciles de ubicar e identificar por los usuarios?] ,[Tipo de dispositivo de paro de emergencia:] ,[¿Se encuentra señalizado y es de fácil acceso?],[¿Se encuentra libre de polvo o material que impida su funcionamiento?], [¿Se encuentra al alcance de cualquier usuario?],[¿Sus componentes se encuentran en buen estado (sin daños, fisuras, roto)?],[¿Sus conexiones estan en buen estado (sin daños, fisuras, roto)?]))>0)

)

Edit: Made one minor change to the expression.

[Msant77s]

Again it takes the expression as valid but it doesn't filter the data 😕

One thing that occurred to me is to be able to use something like this in that expression:

SELECT( from-dataset-column , select-row? [ , distinct-only? ] )

but I don't know where it could be done to make that filter

[Suvrutt_Gurjar]

Please try to create a list with a  consistent pattern.

LIST([Enum 1] , [Enum 2])

Please keep the comma and separation between columns consistent.

[Msant77s]

I don't understand what you mean by creating a good pattern?

[Suvrutt_Gurjar]

Means all elements of the list have same separation between comma and column values. The list included by you is not so and could possibly not work as a list. 

[Msant77s]

This is the expression with the list... it is not throwing me any error, I have already gone through the entire array of columns through Data Explorer and it seems to be correct

AND(IF(ISBLANK(ANY(DashboarfilterPro [SECTOR])), true, ANY(DashboarfilterPro [SECTOR])=[SECTOR]),

IF(ISBLANK(ANY(DashboarfilterPro [Nivel de Riesgo general de máquina o equipo])), true, ANY(DashboarfilterPro [Nivel de Riesgo general de máquina o equipo])=[Nivel de Riesgo general de máquina o equipo]),

IF(ISBLANK(ANY(DashboarfilterPro [Inicio de Consulta])), true, ANY(DashboarfilterPro [Inicio de Consulta])<=[Date]),

IF(ISBLANK(ANY(DashboarfilterPro [Fin de consulta])), true, ANY(DashboarfilterPro [Fin de consulta]) >=[Date]),

(COUNT(LIST([¿Los dispositivos de paro de emergencia son fáciles de ubicar e identificar por los usuarios?],[Tipo de dispositivo de paro de emergencia:],[¿Se encuentra señalizado y es de fácil acceso?],[¿Se encuentra libre de polvo o material que impida su funcionamiento?],[¿Se encuentra al alcance de cualquier usuario?],[¿Sus componentes se encuentran en buen estado (sin daños, fisuras, roto)?],[¿Sus conexiones estan en buen estado (sin daños, fisuras, roto)?]))>0)

)

[Suvrutt_Gurjar]

Please try below

Create a VC in the same table called say [ListAllQ] with the below expression. 

LIST([¿Los dispositivos de paro de emergencia son fáciles de ubicar e identificar por los usuarios?] , [Tipo de dispositivo de paro de emergencia: ] , [¿Se encuentra señalizado y es de fácil acceso?] , [¿Se encuentra libre de polvo o material que impida su funcionamiento?] , [¿Se encuentra al alcance de cualquier usuario?] , [¿Sus componentes se encuentran en buen estado (sin daños, fisuras, roto)?] , [¿Sus conexiones estan en buen estado (sin daños, fisuras, roto)?])

Your filter expression can then be

AND(IF(ISBLANK(ANY(DashboarfilterPro [SECTOR])), true, ANY(DashboarfilterPro [SECTOR])=[SECTOR]),

IF(ISBLANK(ANY(DashboarfilterPro [Nivel de Riesgo general de máquina o equipo])), true, ANY(DashboarfilterPro [Nivel de Riesgo general de máquina o equipo])=[Nivel de Riesgo general de máquina o equipo]),

IF(ISBLANK(ANY(DashboarfilterPro [Inicio de Consulta])), true, ANY(DashboarfilterPro [Inicio de Consulta])<=[Date]),

IF(ISBLANK(ANY(DashboarfilterPro [Fin de consulta])), true, ANY(DashboarfilterPro [Fin de consulta]) >=[Date]),

OR(IN("Si",[ListAllQ]),IN("No",[ListAllQ]))

)

Other Suggestion: You seem to have very long column names with special characters in them. Please evaluate having short columns names. Expressions become easy to manage and will have less errors without special characters. For app users you can have long display names in the app using display name properties.

[Msant77s]

Dear, I have tried that option and it ends up discarding all the rows that have a choice of "NO", it only leaves the rows that have "YES" as an answer...:/

As for the names of the columns, you are right, I was already thinking about changing them, but when I do that, the entire application that is in use at this moment drops. I must wait a few days to be able to make this change.

[Suvrutt_Gurjar]

Thank you for the update. I believe there could be some other issue in your expression that you could analyze.  I think, with the expression OR(IN("Si",[ListAllQ]),IN("No",[ListAllQ])) , the filter should select rows with both Yes and No.

Is it because of the column  [Nivel de Riesgo general de máquina o equipo] used in the filter? Could you update how this column is computed?  

[Msant77s]

Yes, of course!! this column is of type REF to a table that only has three fields "Acceptable", "Moderate" and "Substantial" it has to do with the status of the inspections!!

[Suvrutt_Gurjar]

I think you could also test your slice filter in steps.

Does using only OR(IN("Si",[ListAllQ]),IN("No",[ListAllQ])) in slice filter returns rows with Si and No ?

Then you could add each other condition one by one to check nothing breaks so you can pinpoint the issue better.

[Msant77s]

Excuse me, I have found why it only filtered the values ​​of "YES" and it was because it did not include the first enum, which is the one that divides the advance of the questions!! It still shows blank values ​​anyway.

[Msant77s]

Excuse me, I have found what the error was because it showed me only "YES" values ​​and it was because I did not include the first enum that prevents the advance to the following columns!! anyway it keeps showing me blank values

[Suvrutt_Gurjar]

May we know which is that first Enum column and what enum option values it has?

Does only the first enum prevent advance to the further columns of each enum prevents advance to the next column?

[Msant77s]

That enum prevents the advance to the columns marked in red!! if the answer is "YES" it follows all the fields, if it is "NO" it advances directly to the columns marked in blue, leaving the ones in red empty!

Column Name: [Posee Parada de Emergencia?]

[Suvrutt_Gurjar]

Thank you. Then have you included that column in the [Posee Parada de Emergencia?] VC of  list type of other  concatenated columns?

[Msant77s]

Yes, I have included it and even so it continues to show me the blank values.

[Msant77s]

I don't know if it's possible, but I could share access so you can see the entire application and the settings... maybe I can guide you more to help me solve the problem!

[Suvrutt_Gurjar]

Please remove [Posee Parada de Emergencia?] from VC list column and please test with the below

AND(IF(ISBLANK(ANY(DashboarfilterPro [SECTOR])), true, ANY(DashboarfilterPro [SECTOR])=[SECTOR]),

IF(ISBLANK(ANY(DashboarfilterPro [Nivel de Riesgo general de máquina o equipo])), true, ANY(DashboarfilterPro [Nivel de Riesgo general de máquina o equipo])=[Nivel de Riesgo general de máquina o equipo]),

IF(ISBLANK(ANY(DashboarfilterPro [Inicio de Consulta])), true, ANY(DashboarfilterPro [Inicio de Consulta])<=[Date]),

IF(ISBLANK(ANY(DashboarfilterPro [Fin de consulta])), true, ANY(DashboarfilterPro [Fin de consulta]) >=[Date]),

OR(IN("Si",[ListAllQ]),IN("No",[ListAllQ])),

[Posee Parada de Emergencia?]="Si"

)

I think even following will work

AND(IF(ISBLANK(ANY(DashboarfilterPro [SECTOR])), true, ANY(DashboarfilterPro [SECTOR])=[SECTOR]),

IF(ISBLANK(ANY(DashboarfilterPro [Nivel de Riesgo general de máquina o equipo])), true, ANY(DashboarfilterPro [Nivel de Riesgo general de máquina o equipo])=[Nivel de Riesgo general de máquina o equipo]),

IF(ISBLANK(ANY(DashboarfilterPro [Inicio de Consulta])), true, ANY(DashboarfilterPro [Inicio de Consulta])<=[Date]),

IF(ISBLANK(ANY(DashboarfilterPro [Fin de consulta])), true, ANY(DashboarfilterPro [Fin de consulta]) >=[Date]),

[Posee Parada de Emergencia?]="Si"

)

[Msant77s]

Now it returns to filter only the rows that have a "YES" in the first enum

[Msant77s]

I have tried both ways you mentioned.

[Suvrutt_Gurjar]

What does only [Posee Parada de Emergencia?]="Si" as filter condition shows?

[Msant77s]

clear only shows the records that have "YES" in that response to enum1

[Suvrutt_Gurjar]

Then those records are what you wish to see, correct? of course with other date and one more enum condition in filter.

[Msant77s]

In reality, what I need is for it to put all the records, but in the graphs following enum1 it does not show me the blank spaces (that is, those that are empty due to the first response to the enum). Since it should not appear as representative data since they do not exist due to the "NO" response of that first enum list

[Suvrutt_Gurjar]

Correct, since we have condition [Posee Parada de Emergencia?]="Si" in the filter, all the records where other enums are Blank because [Posee Parada de Emergencia?]="No" should not show at all. That is why I am requesting there could be some issue with the other part of the filter expression.

[Msant77s]

Nono, I misunderstood your question then!! when using that filter "[Has Emergency Stop?]="Yes" " it only shows me the rows to which that question was yes! but it doesn't show me 85% of the records that are also important in displaying the questions prior to those enums...because the form comes with many previous questions about machine conditions!! When putting that condition that you express to me, it only leaves me the machines that have an emergency stop... but it does not show me all the other machines that were previously evaluated and that have other data of interest!

With this condition, the graphics panel looks like this:

If you compare one panel with another, you can see that it eliminates all the records that carry a previous evaluation that is important to see in the graphs!

[Msant77s]

If you look at this image you can see that the filters work correctly... By machine sector, gravity and time!! but it shows me the blank values ​​that I don't want to see

[Suvrutt_Gurjar]

Oh that is a very complex condition. I will also think over it. Basically when [Posee Parada de Emergencia?]="No" you do not wish to show only certain enum columns but you want to show other columns in that row in the dashboard , correct?

[Msant77s]

Of course that's how it is... ultimately when the answer is NO to that enumlist I need it not to count in the graphs of that row the subsequent columns that I marked previously, but it does continue to show me the previous columns that were evaluations of that same team .

[Suvrutt_Gurjar]

For those enum columns that you do not wish to show , please try the following in show_if constraint of the columns

AND( CONTEXT("View")="Dashboard View Name",[Posee Parada de Emergencia?]="Si")

Please try with one column and test. 

I think otherwise you may need to create individual slices.