Forum Discussion

johnbird's avatar
johnbird
Regular Visitor
2 years ago
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...
Need Help
  • johnbird's avatar
    johnbird
    2 years ago

    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"))
    )

     

johnbird's avatar
johnbird
Regular Visitor
2 years ago

I have attached below a picture of my data. Basically this is a calculated tables that work out the status of an issue. The is a master table that contain all the issues listed by month on each row. This table I have set it so month_0 is the current month and month_1 is the previous month etc. This was the best way I could think how to do it. As the master table contains passed and failed results and I am just interested in the failed.

  • johnbird's avatar
    johnbird
    Regular Visitor
    2 years ago

    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"))
    )