Ability to select items in a table and masked unselected item for display that refreshes daily

[peteru9067]

My Power BI is tied to a database that records the daily trips in my Plant...... the database is refreshed every 12hrs. I have developed a dashboard using PowerBI that records all the trips, however some of the trips are "not real" since they are as a result of the first-out trip. So I want to be able to select the approved/valid trips for the day and masked the others. See example below

Table A

Trip Tag	Start Time	End Time
15-PXSLL-0001	12/26/2016 6:13:14 PM	12/26/2016 10:53:14 PM
15-TXSLL-0202	12/26/2016 6:13:15 PM	12/26/2016 10:53:14 PM
15-TXSLL-0005	12/26/2016 6:13:15 PM	12/26/2016 10:53:14 PM
15-FXSLL-4009	12/26/2016 9:13:14 PM	12/26/2016 10:53:14 PM

Table B........ I want to have the ability to "Select" (maybe in the form of a checkbox) of the Trips to disply on my dashboard i.e. among the trip tags in the table above I only want to show two of the tags.... the others are irrelevant...... and I dont want to lose that info when the database refreshes.

Trip Tag	Start Time	End Time
15-PXSLL-0001	12/26/2016 6:13:14 PM	12/26/2016 10:53:14 PM
15-FXSLL-4009	12/26/2016 9:13:14 PM	12/26/2016 10:53:14 PM

[v-angzheng-msft]

Hi, @peteru9067 

I'm sorry I may not be clear on what you are trying to achieve

What does the "Select" above mean?  Does using a Slicer help?

---

Table B........ I want to have the ability to "Select" (maybe in the form of a checkbox) of the Trips to disply on my dashboard i.e. among the trip tags in the table above I only want to show two of the tags.... the others are irrelevant...... and I dont want to lose that info when the database refreshes.

---

Could you please consider sharing more details about it and posting expected result so it is clear on what needs to be implemented?  And It would be great if there is a sample file without any sesentive information here.

It makes it easier to give you a solution.

1. Sample (dummy dataset) data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[peteru9067]

I have investigated and the functionality does not exist....... online search has shown that many people are also interested in this functionality, for users to be able to manually select or enter data in cell without creating a formular in the new column. Again Thanks

[BA_Pete]

Hi @peteru9067 ,

Did you try my Excel approved list suggestion above? How did you get on?

To be honest, I'd be very surprised if the devs implemented any manual-entry functionality beyond what's already there i.e. 'Enter Data' and 'Column from Examples' functions. PBI isn't meant to be a new source of truth, it's meant to visualise the truth that you hold elsewhere.

One possibility you could explore is to use a PowerApp window to write back to your data source, but

a) This would need to be displayed in the report, so would allow anyone to write back.

b) This can get very technical and requires a certain level of source permissions to enable.

Pete

[peteru9067]

I think it is getting traction though...... if you search online you will see people have been advising Power BI to develop that functionality. For instance, some KPIs (Key Performance Indicators) are based on findings from a Root Cause Analysis (RCA). There are no set formular to determine the actual cause for a problem unless a team comes together and validate the cause. Hence this validated/approved "cause" in a list of dependant "causes" is the one to display in a dashboard. And this also changes over time. So in a Power or Process Plant where you have several trips or downtime per year, there may exist thousands of dependant trips but I only want to display the "main" trips which was derived after a RCA investigation. Hence instead of listing 1000 trips per year I only have 20 Trips.

[BA_Pete · Accepted solution]

Hi @peteru9067 ,

There's a few things you can do here, depending on you exact requirements. Some options:

1) Use Power Query to filter the table based on identifiable criteria. E.g. your example shows that trips with 'TX' in the Trip Tag have been removed. You could apply a table filter like:

Table.SelectRows(previousQueryStep, each not Text.Contains([Trip Tag], "TX"))

2) Do the sam as the above, but as a visual/page/report-level filter. Use the filter pane on the right of your report page to add your [Trip Tag] field, then select 'Advanced filtering' and 'Does not contain'.

3) Apply a slicer to the report page using [Trip Tag] as the value. Select the slicer, go to Selection Controls and turn 'Multi-select with CTRL' OFF. This will allow you/enduser to select/deselect trips with checkboxes. If you enable endursers to save filters, these selections will be retained on refresh.

Pete

[peteru9067]

Thanks Pete but that is not exactly what I wanted....... I wanted to be able to select tags based on approval or validation criteria (which is typically manual). Here is the scoop...... In my Process Plant Trips are interdependent, meaning due to a Pump stopping, I will register a Low Flow Trip, a Low Pressure Trip and probably a Low Vessel level. So in this scenario I have 3 other trips (Flow, Level, and Pressure), however they are all as a result of the Pump stopping. So I do not want to account for those trips, I only want the Pump stopping trip, which I will select for display on my dashboard for that particular day. And the next day it may be something else.

I really dont know if this is even possible to accomplish in Power BI.

Thanks

[BA_Pete]

Hi @peteru9067 ,

Ok, I see.

In terms of 'possible': it's always possible, it just depends on what lengths you are willing to go to achieve it.

In terms of the validation process, if you wanted to keep this as a manual process then you are looking at either a manual filter in PQ, then a re-publish of the report, or the checkbox slicer solution.

Power BI doesn't lend itself that well to manual interventions.

However, I think this could and should be done dynamically.

Are you able to get a pump ID into your table? If so, I think we could work out which row you wanted to keep based on the pump ID and the start time.

We could also look at prioritising trip types. For example, if PumpA has four trip flags within 1 minute of each other, then if 'Pump Stop' type is present, display this and exclude the others etc.

Pete

[peteru9067]

Hi Pete..... oh no, pump is just an example we have over 3,000 things i.e. devices, processes etc that can trip the plant..... tomorrow, it could be a high pressure in a vessel or a valve failing to the close position. once these things happen, then there are many other things that could occur due to the initial failure. So I want to be able to review the list once it comes into Power BI from the data source (in my case that will be SQL) and then either at the Query Editor or Dashboard be able to select the ones I want to display. Again there is no set structure for the selection, this is the process that is done manually. I evaluate or investigate the Root Cause for the Trip and then pick a single or multiple tags as the initiator. This is what I want to display while the other tags are masked

[BA_Pete]

Okay. If the manual procss is a deal-breaker, then I think the best balance you'll get is as follows:

Create an Excel file that only includes a list of trip tags that you want to keep i.e. as you go through the list manually checking them, type/copy the trip tag to your Excel 'approved' tag list. Import this excel file into PQ, INNER merge it onto your original table on originalQuery[Trip Tag] = excelList[Trip Tag], to leave only 'approved' tags in the table.

This, I think, is the best balance: You get to keep your manual control over exactly which tags you want to display, but the Power BI side of it remains 'dynamic' insofar as you won't need to touch PQ/PBI, it will just pick up the latest 'approved' list from Excel each time it refreshes.

Pete

[peteru9067]

I guess one way is adding another column in my dashboard or query that I can manually enter Yes or No....... or just "yes" and the blanks will be considered "no"...... but how do I enter a custom column that will be used for manual data entry, I guess. Most columns require a formular