Create a custom table

[johnbird]

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

[Ritaf1983]

Hi @johnbird 

Can you please share a pbix or some dummy data that keeps the raw data structure with expected results? It should help us clarify your scenario and test the coding formula.

How to Get Your Question Answered Quickly  

[johnbird]

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.

[Ritaf1983]

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

[johnbird · Accepted solution]

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

)