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.
Bear with me as I'm still getting my head out of excel and into PowerBI.
So I have a table (DefectData) that captures when a defect occured (DefectStart) and when it was fixed (DefectEnd) against one of four machines in the (Plant) column. The end goal in a stacked column visual that shows when the defects occured. So far I've....
So far, so good. Now I need to take the MyCal table and fill in the dates bewteen the DefectStart and DefectEnd with a boolean for each of the machine columns. This is where i get fuzzy on my PowerBI knowledge.
I pivoted off the (Plant) column to get two tables with the booleans for DefectStart and DefectEnd of each of the indiviual machine columns (Plant N1, Plant N2, etc.). I need to join these two tables and my access brain is screaming to introduce a primary key, but do i need to do this?? And then how to i stitch this against the MyCal dates and fill in the entries between DefectStart/DefectEnd??
Any little helpers are greatly appreciated.
@DangerDraper , if you do not have Single column to join. You can create concatenated column and jin them
Key = [Column1] & "-" & [Column2]
For between dates refer if this can help : https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
thanks @amitchandak but there's issues with that, probably based upon my lack of description. The calendar table is already dynamically created (see MyCal in post).
To visualise it better, the root data set (DefectData) looks like this:
Plant | DefectStart | DefectEnd |
Plant N1 | 01-01-2001 | 04-01-2001 |
... | ... | ... |
The calendar table (MyCal) is generated off two querries, as described earlier:
CalDate |
01-01-2001 |
... |
28-10-2017 |
Back to (DefectData), pivoting off the (Plant) column will change the data to either of these two tables:
DefectStart | Plant N1 | .... | Plant N4 |
01-01-2001 | 1 | ... | 0 |
... | ... | ... | ... |
... | ... | ... | ... |
02-10-2017 | 0 | .... | 1 |
Or
DefectEnd | Plant N1 | .... | Plant N4 |
03-01-2001 | 1 | ... | 0 |
... | ... | ... | ... |
... | ... | ... | ... |
30-10-2017 | 0 | .... | 1 |
For sanity sake, i create and save them both as (DefectStartTable) and (DefectEndTable).
The desired end is to construct the following:
CalDate | Plant N1 | ... | Plant N4 |
01-01-2001 | 1 | ... | 0 |
02-01-2001 | 1 | ... | 0 |
... | ... | ... | |
28-10-2017 | 0 | ... | 1 |
29-10-2017 | 0 | ... | 1 |
30-10-2017 | 0 | ... | 1 |
From there, the visualisation aspects are fine. It more a matter of how do I transform the data natively within PowerBI without resorting to pre-transforming it in the original source. The real trick is how do I create a positive response for dates between a defect (ie. 29-10-2017), other wise the visualisation won't know to join the start/end dates.
I initially tried the root data (DefectData) as a gantt visual but the successive defects cascaded down the visual as seperate instances and not reoccurring instaces against the (Plant).
Hope that this better explains the dilema.
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 |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |