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

     

ThxAlot's avatar
ThxAlot
Icon for Super User rankSuper User
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] )
)