Forum Discussion

JIRAMON's avatar
JIRAMON
Frequent Visitor
3 years ago
Solved

Create new table with: ADDCOLUMN, SELECTCOLUMN, FILTER

Hi all,   I have been trying without success to create a new table that 1st adds 2 columns from 2 related tables, then selects some of the columns from the resulting table, and then Filters. I expl...
DAX
powerbi
  • ThxAlot's avatar
    ThxAlot
    3 years ago

    Latest Invoice.pbix

     

    Leverage physic relationships for better performance,

     

    Latest = 
ADDCOLUMNS(
    CALCULATETABLE(
        'Invoice Lines',
        FILTER(
            Invoices,
            Invoices[Invoice Date]
                = CALCULATE(
                    MAX( Invoices[Invoice Date] ),
                    ALLEXCEPT( Invoices, Invoices[Customer ID] )
                )
        )
    ),
    "Cust ID", RELATED( Invoices[Customer ID] ),
    "Inv Date", RELATED( Invoices[Invoice Date] ),
    "Item Name", RELATED( Items[Item Name] )
)

     

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
3 years ago

Hi,

I am not sure if I understood your question correctly,  but pleas try something like below.

Please check the below picture and the attached pbix file.

 

 

 

Expected result table = 
VAR _latestinvoicebycustomer =
    GROUPBY (
        Invoices,
        Invoices[Customer ID],
        "@lastestinvoice", MAXX ( CURRENTGROUP (), Invoices[Invoice ID] )
    )
RETURN
    CALCULATETABLE (
        SUMMARIZE (
            'Invoice lines',
            Invoices[Invoice ID],
            Invoices[Customer ID],
            Invoices[Invoice Date],
            Items[Item ID],
            Items[Item Name],
            'Invoice lines'[Quantity],
            'Invoice lines'[SalesAmount]
        ),
        TREATAS (
            _latestinvoicebycustomer,
            Invoices[Customer ID],
            Invoices[Invoice ID]
        )
    )