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 and column. I have tried this but cannot get it to work for more rows.

 

dq_test =

    GENERATE(
        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"),
        "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"),
        "Resolved", calculate(COUNTA(dq_issues[BindingID]),dq_issues[Month_1_check] = "Resolved"))
)
 
If I take out the generate and 2nd Row it work. I want to know how I can add multiple calculated row.
 
Any help would be greatly appreciated. Or if there is a better way. 
 
Thanks John
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"))
    )

     

4 Replies

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