Create a table connected to the main database to keep a log of comments and changes

[pratafran]

Hello,

I have a model where I keep a track of all the shipments with material that is moving from the production facilities to final destination. This model also has a set of measures with alarms when I have a delay vs the standard transit time or I expect to have a document that I still do not have (for example, a shipment close to destination port that has not customs docs yet).

I want to create a "log of comments" to monitor those alarms and the actions taken to solve them.

Imagine that I have a team of several analysts entering to the model, checking the alarms and doing something (for, example, sending en email to a supplier, requesting a doc, etc etc).

Now I'm managing this with a "comments" column in the database, but I want a kind of "Log" with dates and actions done for each shipment that has an alarm.

Something like:

Shipment ID 1

05/01: mail sent to XXX@XXX. Customs docs requested

05/05: Docs received, pending release from terminal, waiting for Pickup # from XXX

05/07: cargo released

Is there a way to achieve that?. I guess with a Log Table connected by ID to the main table with Edit date, Shipment ID, and comment

Is possible to enter those comments directly from the Web Power BI? or the only way is by working on the source database?

I imagine to work similar as a "call center", when an analyst takes your call, and see the previous log of your inquiries to understand the status of your problems as a customer.

Thanks

[v-juanli-msft]

Hi @pratafran 

Could you show me some example data?

I think comments can be added in a measure or a calculated column based on your original data.

Best Regards

Maggie

[pratafran]

Hi @v-juanli-msft 

Something like this:

The first shipment is already delivered with 9 days of delay. The alarm is N because it is a completed trip. In the second table I have the log of actions that were taken while it was in transit, related by the shipment ID

The second shipment is OK

The third shipment is in transit and with the ETA vs the ETA STD, I expect a delay. In the second table I have also the log of actions I'm taking to speed up the shipment.

I want a way of generating the second table with the log of changes as much automatic as possible. So an user can filter the shipments with alarms, do actions and record them in the log with the current date.

Thanks for your help!! 🙂

[v-juanli-msft]

Hi @pratafran 

If you'd like a report like this:

Create measures in logtable,

lastest date = CALCULATE(MAX(logtable[date]),FILTER(ALLSELECTED(logtable),logtable[shipment id]=MAX(logtable[shipment id])))

flag = IF(MAX(shipments[status])="in transit"&&MAX(shipments[alarm])="y",1,0)

current commment = IF([flag]=1,CALCULATE(MAX(logtable[comment]),FILTER(ALLSELECTED(logtable),logtable[date]=[lastest date])),"no log")

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[pratafran]

Thanks!, that works but how is the log data supposed to be filled?. I aim to pusblish the PBI in web, and to make the analyst able to check the events with alarms and enter the comments directly from web into the BI (instead of editiing the desktop version).

I was googling it and as far as I could see, I need external solutions as PowerApps

https://stackoverflow.com/questions/51611281/input-data-directly-through-power-bi-published-report

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/powerapps-custom-visual

https://powerapps.microsoft.com/en-us/

[v-juanli-msft]

Hi @pratafran 

You and your members could add comments to a dashboard or report in power bi service.

But this comments isn't added to your data model, so the transformation or calculation don't work for it.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.