Adding new rows to report

[ITManuel]

Hi,

I've got the following data model.

The table "AInvoiceCalc" is a calculated table using  SUMMARIZE with an outer ADDCOLUMNS to add new columns. 

.........
VAR AInvoicesFilteredSummarized =
    SUMMARIZE (
        AInvoicesFiltered,
        AInvoices[PROJECTCODE],
        AInvoices[CUSTOMERCODE],
        AInvoices[INVOICECODE]
    )
RETURN
    ADDCOLUMNS (
        ADDCOLUMNS (
            ADDCOLUMNS (
                AInvoicesFilteredSummarized,
                "InvoiceRegister", CALCULATE ( MAX ( AInvoices[REGISTER] ) ),
.........

As a result "AInvoiceCalc" contains various informations about invoicing and payments structured by the structure defined by SUMMARIZED.

Following result table filtered by one project as an example: (not all colums visible)

The columns of AInvoiceCalc are then used directly in a report or used for further calculations in measures used for the report.

Below example of the report showing a Project and all the invoices (InvoiceID's for example 191584) with informations of invoiced amount, paid amount, outstanding amount and overdue amount.

The informations of paid amounts are retrieved from the CashIN table which has a relationship with the AInvoices table. The CashIN table contains only paments which are related to an Invoice contained in the AInvoices table. 

So far so good. 

The issue I have is with the CashINNoInv table. It contains payments which are not related to any invoice instead the payments contain the related project but no reference to any invoice.

For the project in the above matrix there is a payment in CashINNoInv for which I would like to show an additional row in the report which does contain the paid amount and a blank for the InvoiceID as there is not Invoice reference. 

Considering that the table AInvoiceCalc is structured also by  AInvoices[INVOICECODE] --> so Invoices, I have issue to achieve this.

Any help is much appreciated.

Best regards

[lbendlin]

" I would like to show an additional row in the report"

That is very hard to do in Power BI. You would have to maintain two separate tables , one with payments that are related to invoices, and one with payments that are not. Then you would need to append these tables in Power Query, and then use filters to not show certain rows.

In Power BI You can dynamically "remove"  (filter) content. You cannot dynamically add content.

[ITManuel]

Hi @lbendlin ,

actually I tried to append the tables Power Query, but it did not help. The report is strucuted by a Project / Invoice hierarchy. If I cannot add somehow a row to the "AInvoiceCalc" table or even "only" in the report, then what I would like to do might not be possible as you say. I might try at a later stage once again.

In relation to dyamically filter content I would to ask you the following.

Now I have a slightly changed data model:

I have created an additional table which returns one column only containig any INVOICECODE fully compensated by a creditnote. That table "FilterFullyCompensatedInv" has a relationship with the table "AInvoiceCalc". 

I would like to filter "AInvoiceCalc" in order that it does not contain any Invoices which are contained in "FilterFullyCompensatedInv" if possible only for one page of the report, for other pages "AInvoiceCalc" should not be filtered. 

Best regards

[lbendlin · Accepted solution]

That you can do - for example with a measure that you then add to the page level filters.