Forum Discussion
Solved
Create a custom table
Hello, I have been stuck on this for a few hours now. I am trying to create a new table ("dq_test") with 4 column: Month, New, Outstanding, Resolved. I want to calculate the value for each row an...
Found it I was using the UNION wrong. You just need to do this:
dq_test =UNION(ROW("Month", FORMAT(EOMONTH(MAX(Master[ReportingPeriod]),0),"MMMM YY"),"New", calculate(COUNTA(dq_issues[BindingID]),dq_issues[Month_0_check] = "New"),"Outstanding", calculate(COUNTA(dq_issues[BindingID]),dq_issues[Month_0_check] = "OutStanding") + calculate(COUNTA(dq_issues[BindingID]),dq_issues[Month_0_check] = "New"),"Resolved", calculate(COUNTA(dq_issues[BindingID]),dq_issues[Month_0_check] = "Resolved")),ROW("Month", FORMAT(EOMONTH(MAX(Master[ReportingPeriod]),-1),"MMMM YY"),"New", calculate(COUNTA(dq_issues[BindingID]),dq_issues[Month_1_check] = "New"),"Outstanding", calculate(COUNTA(dq_issues[BindingID]),dq_issues[Month_1_check] = "OutStanding") + calculate(COUNTA(dq_issues[BindingID]),dq_issues[Month_1_check] = "New"),"Resolved", calculate(COUNTA(dq_issues[BindingID]),dq_issues[Month_1_check] = "Resolved")))
Hi johnbird
You need to unpivot this table in data source / with power query,
To bring it to the form of
Id, month , status.
After this you can convert the months to dates and create a standart star schema.
More information about unpivot here :
https://youtu.be/ESap6ptV8fI?si=z29lceoinXhodB38
About star schema :
https://youtu.be/KUrhiIGiea4?si=qySa9AXv1zCLxr-w
Found it I was using the UNION wrong. You just need to do this:
dq_test =
UNION(
ROW(
"Month", FORMAT(EOMONTH(MAX(Master[ReportingPeriod]),0),"MMMM YY"),
"New", calculate(COUNTA(dq_issues[BindingID]),dq_issues[Month_0_check] = "New"),
"Outstanding", calculate(COUNTA(dq_issues[BindingID]),dq_issues[Month_0_check] = "OutStanding") + calculate(COUNTA(dq_issues[BindingID]),dq_issues[Month_0_check] = "New"),
"Resolved", calculate(COUNTA(dq_issues[BindingID]),dq_issues[Month_0_check] = "Resolved")),
ROW(
"Month", FORMAT(EOMONTH(MAX(Master[ReportingPeriod]),-1),"MMMM YY"),
"New", calculate(COUNTA(dq_issues[BindingID]),dq_issues[Month_1_check] = "New"),
"Outstanding", calculate(COUNTA(dq_issues[BindingID]),dq_issues[Month_1_check] = "OutStanding") + calculate(COUNTA(dq_issues[BindingID]),dq_issues[Month_1_check] = "New"),
"Resolved", calculate(COUNTA(dq_issues[BindingID]),dq_issues[Month_1_check] = "Resolved"))
)