Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello all!
I inherited the graph below which uses the calculate function to create a planned execution graph (Test Exeuction Progress - Bottom Left Graph). The light blue line is our weekly planned scripts for testing which uses "Planned Execution Date" to create the line. However, the team I work with is now looking to consider both "Planned Execution Date" or an "LBE Planned Execution Date" if populated. This data is sourced from a SharePoint list where these values are two separate columns.
Code:
The prior owner of this basically created multiple line items for each record which feeds into the formula above. I was able to update the query to create a line item for LBE Planned Execution. However, I am only able to use LBE or the original planned date to create the graph.
Is there a way to add logic to the function to consider the original planned date or the LBE planned date where it is populated?
Please let me know if these additional inputs help.
When the data is brought in to PowerBI, multiple line items are generated for each ID (this data set comes from a SharePoint list). A few transformations are run to get to the data set below:
= Table.RenameColumns(#"Unpivoted Columns1",{{"Attribute", "Date Category"}, {"Value", "Date"}})
= Table.AddColumn(#"Renamed Columns1", "Event Type", each if Text.Contains([Date Category], "LBE") then "LBE" else if Text.Contains([Date Category], "Execution") then "Execute" else if Text.Contains([Date Category],"Approval") then "Complete" else null)
To then get to the graph above (planned line), the following formula is used. For the X-Axis, a simple week end date was set up.
So this is where my issue is, if I replace "Event Type" with "LBE", it only shows records that have an LBE date. But, if I leave it as "Execute", it does not consider the LBE. Because of the multiple lines for each record, I have not been able to find a way to consider LBE if it exists but also leave in the original date if LBE doesn't exist.
Id | Date Category | Date | Event Type | Event Value |
5570 | PlannedApprovalDate | 03/15/23 | Complete | Planned |
5570 | ActualExecutionDate | 03/10/23 | Execute | Actual |
5570 | LBEPlannedExecutionDate | 03/17/23 | LBE | Planned |
5570 | PlannedExecutionDate | 03/08/23 | Execute | Planned |
5571 | PlannedApprovalDate | 04/06/23 | Complete | Planned |
5571 | PlannedExecutionDate | 03/30/23 | Execute | Planned |
5572 | ActualExecutionDate | 02/28/23 | Execute | Actual |
5572 | PlannedExecutionDate | 02/13/23 | Execute | Planned |
5573 | ActualExecutionDate | 02/28/23 | Execute | Actual |
5573 | PlannedExecutionDate | 03/06/23 | Execute | Planned |
5574 | ActualExecutionDate | 02/24/23 | Execute | Actual |
5574 | PlannedExecutionDate | 02/20/23 | Execute | Planned |
5575 | ActualExecutionDate | 02/24/23 | Execute | Actual |
5575 | PlannedExecutionDate | 02/20/23 | Execute | Planned |
5576 | ActualExecutionDate | 02/28/23 | Execute | Actual |
5576 | PlannedExecutionDate | 02/20/23 | Execute | Planned |
5577 | PlannedExecutionDate | 02/20/23 | Execute | Planned |
5577 | ActualExecutionDate | 02/27/23 | Execute | Actual |
5578 | PlannedExecutionDate | 02/20/23 | Execute | Planned |
5578 | ActualExecutionDate | 02/27/23 | Execute | Actual |
5606 | ActualApprovalDate | 03/15/23 | Complete | Actual |
5606 | PlannedApprovalDate | 04/11/23 | Complete | Planned |
5606 | ActualExecutionDate | 03/14/23 | Execute | Actual |
5606 | LBEPlannedExecutionDate | 03/17/23 | LBE | Planned |
5606 | PlannedExecutionDate | 04/10/23 | Execute | Planned |
5607 | ActualApprovalDate | 03/01/23 | Complete | Actual |
5607 | PlannedApprovalDate | 03/30/23 | Complete | Planned |
5607 | PlannedExecutionDate | 03/27/23 | Execute | Planned |
5608 | ActualApprovalDate | 02/07/23 | Complete | Actual |
5608 | PlannedApprovalDate | 03/30/23 | Complete | Planned |
5608 | ActualExecutionDate | 02/05/23 | Execute | Actual |
5608 | PlannedExecutionDate | 03/27/23 | Execute | Planned |
5610 | ActualApprovalDate | 03/01/23 | Complete | Actual |
5610 | PlannedApprovalDate | 03/20/23 | Complete | Planned |
5610 | LBEPlannedExecutionDate | 03/17/23 | LBE | Planned |
5610 | PlannedExecutionDate | 03/13/23 | Execute | Planned |
5611 | ActualApprovalDate | 03/01/23 | Complete | Actual |
5611 | ActualExecutionDate | 03/06/23 | Execute | Actual |
5611 | PlannedExecutionDate | 03/06/23 | Execute | Planned |
5612 | ActualApprovalDate | 02/08/23 | Complete | Actual |
5612 | ActualExecutionDate | 02/01/23 | Execute | Actual |
5612 | PlannedExecutionDate | 02/20/23 | Execute | Planned |
5615 | PlannedExecutionDate | 03/13/23 | Execute | Planned |
5616 | PlannedExecutionDate | 03/30/23 | Execute | Planned |
5617 | ActualExecutionDate | 03/10/23 | Execute | Actual |
5617 | LBEPlannedExecutionDate | 03/10/23 | LBE | Planned |
5617 | PlannedExecutionDate | 02/27/23 | Execute | Planned |
5618 | PlannedExecutionDate | 03/03/23 | Execute | Planned |
5623 | PlannedExecutionDate | 03/17/23 | Execute | Planned |
5624 | PlannedExecutionDate | 03/17/23 | Execute | Planned |
5625 | PlannedExecutionDate | 03/17/23 | Execute | Planned |
5627 | PlannedExecutionDate | 03/13/23 | Execute | Planned |
5628 | PlannedExecutionDate | 03/22/23 | Execute | Planned |
5630 | PlannedExecutionDate | 03/17/23 | Execute | Planned |
5630 | ActualExecutionDate | 03/09/23 | Execute | Actual |
5631 | PlannedExecutionDate | 03/17/23 | Execute | Planned |
5632 | LBEPlannedExecutionDate | 03/30/23 | LBE | Planned |
5632 | PlannedExecutionDate | 03/17/23 | Execute | Planned |
5633 | LBEPlannedExecutionDate | 03/30/23 | LBE | Planned |
5633 | PlannedExecutionDate | 02/27/23 | Execute | Planned |
5634 | LBEPlannedExecutionDate | 03/30/23 | LBE | Planned |
5634 | PlannedExecutionDate | 03/20/23 | Execute | Planned |
5635 | LBEPlannedExecutionDate | 03/30/23 | LBE | Planned |
5635 | PlannedExecutionDate | 03/17/23 | Execute | Planned |
5636 | LBEPlannedExecutionDate | 03/30/23 | LBE | Planned |
5636 | PlannedExecutionDate | 03/17/23 | Execute | Planned |
5638 | LBEPlannedExecutionDate | 03/30/23 | LBE | Planned |
5638 | PlannedExecutionDate | 03/17/23 | Execute | Planned |
5639 | LBEPlannedExecutionDate | 03/30/23 | LBE | Planned |
5639 | PlannedExecutionDate | 02/27/23 | Execute | Planned |
5640 | LBEPlannedExecutionDate | 03/30/23 | LBE | Planned |
5640 | PlannedExecutionDate | 02/27/23 | Execute | Planned |
5641 | PlannedExecutionDate | 03/02/23 | Execute | Planned |
5642 | PlannedExecutionDate | 04/03/23 | Execute | Planned |
5643 | PlannedExecutionDate | 03/28/23 | Execute | Planned |
5644 | PlannedExecutionDate | 03/17/23 | Execute | Planned |
5647 | ActualExecutionDate | 03/13/23 | Execute | Actual |
5647 | PlannedExecutionDate | 03/17/23 | Execute | Planned |
5648 | PlannedExecutionDate | 04/10/23 | Execute | Planned |
5649 | PlannedExecutionDate | 04/10/23 | Execute | Planned |
5651 | PlannedExecutionDate | 03/03/23 | Execute | Planned |
5661 | PlannedApprovalDate | 03/20/23 | Complete | Planned |
5661 | PlannedExecutionDate | 03/21/23 | Execute | Planned |
5662 | PlannedApprovalDate | 03/17/23 | Complete | Planned |
5662 | LBEPlannedExecutionDate | 03/13/23 | LBE | Planned |
5662 | PlannedExecutionDate | 03/13/23 | Execute | Planned |
5663 | PlannedApprovalDate | 03/17/23 | Complete | Planned |
5663 | LBEPlannedExecutionDate | 03/13/23 | LBE | Planned |
5663 | PlannedExecutionDate | 03/14/23 | Execute | Planned |
5664 | PlannedApprovalDate | 03/17/23 | Complete | Planned |
5664 | LBEPlannedExecutionDate | 03/13/23 | LBE | Planned |
5664 | PlannedExecutionDate | 03/16/23 | Execute | Planned |
5665 | PlannedApprovalDate | 03/17/23 | Complete | Planned |
5665 | LBEPlannedExecutionDate | 03/13/23 | LBE | Planned |
5665 | PlannedExecutionDate | 03/16/23 | Execute | Planned |
5666 | PlannedApprovalDate | 03/30/23 | Complete | Planned |
5666 | LBEPlannedExecutionDate | 03/27/23 | LBE | Planned |
5666 | PlannedExecutionDate | 03/27/23 | Execute | Planned |
5667 | PlannedApprovalDate | 03/30/23 | Complete | Planned |
5667 | LBEPlannedExecutionDate | 03/27/23 | LBE | Planned |
5667 | PlannedExecutionDate | 03/17/23 | Execute | Planned |
Read about COALESCE()
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
Hi , @msj72432
According to your description, you need to analyze the planned date or the LBE planned date .
If this , i think you can try to use the field parameter as a dimension for the x-axis.
For more information, you can refer to :
Let report readers use field parameters to change visuals (preview) - Power BI | Microsoft Learn
Fields parameters in Power BI - SQLBI
If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
83 | |
70 | |
65 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |