Data Shaping Help?

[DangerDraper]

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....

• merge querry DefectStart/DefectEnd and drill down to dynamically get earliest and latest values (CalStart & CalEnd)
• create a date table (MyCal) by = {Number.From(CalStart)..Number.From(CalEnd)}
• create a relationship between (MyCal) and (DefectData) thru the date

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.

[amitchandak]

@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/

[DangerDraper]

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.