Help with DAX Code

[PietroFarias]

I need help with the DAX below:

EVALUATE
CALCULATETABLE (
    SUMMARIZE (
        fVendas,
        dadosFILIAL[ID],
        fVendas[NF],
        "PRODUCT 1", CALCULATE (
            [Volume do Periodo],
            FILTER ( dProdutos, dProdutos[typeProduct] = "PRODUCT 1" )
        ),
        "PRODUCT 2", CALCULATE (
            [Volume do Periodo],
            FILTER ( ALL ( dProdutos ), dProdutos[typeProduct] = "PRODUCT 2" )
        )
    ),
    FILTER ( dProdutos, dProdutos[BRAND] = "BRAND X" )
)

I applied a FILTER together with the CALCULATE TABLE of the Products table (dProducts), returning only the products of the brand 'brand x'.

In Column 'PRODUCT 1' contains the sum of the volume sold of 'PRODUCT 1'. There are 'PRODUCT 1' within the products table where you have the 'brand x'. OK

The problem is:
The 'PRODUCT 2' will NEVER have an 'brand x'. Thinking about it, I used an ALL (dProdutos) to not suffer the filter. But, the calculation keeps returning empty.

What am I doing wrong?

[PietroFarias · Accepted solution]

Thanks for all.

I read 1000x and change the DAX and it worked.

EVALUATE
CALCULATETABLE (
ADDCOLUMNS(
    SUMMARIZE (
        fVendas,
        dadosFILIAL[ID],
        fVendas[NF],
        "PRODUCT 1", CALCULATE (
            [Volume do Periodo],
            FILTER ( dProdutos, dProdutos[typeProduct] = "PRODUCT 1" )
        )
    ),"PRODUCT 2", 
        CALCULATE (
            [Volume do Periodo],
            FILTER ( ALL ( dProdutos ), dProdutos[typeProduct] = "PRODUCT 2" )
        )
),
    FILTER ( dProdutos, dProdutos[BRAND] = "BRAND X" )
)

As I understand it, when taking it out of the SUMMARIZE, he can apply ALL (dProduct). And within the SUMMARIZE he already received the FILTER from CALCULATETABLE before performing ALL. I don't know if I'm talking nonsense.

[edhans]

Your Summarize is returning a table.

Then your Calculate Table is taking that table and filtering out products without brand X, and that filter is propigating to the SUMMARIZE table.

to see this, get rid of the CALCULATETABLE and FILTER function, Just look at what your SUMMARIZE is doing first. If that is ok, then you'll need to do something else in the FILTER.

For any further assistance, you'll need to link to a PBIX file in ONeDrive or Dropbox or similar. Don't put tables in a post. Your model needs to be examined. Remove any confidientail info or create a model with fake data.

[amitchandak]

@PietroFarias ,

I think the filter on the top table is removing it. The only measure which can get filter is Product 1, move filter there

SUMMARIZE (
        fVendas,
        dadosFILIAL[ID],
        fVendas[NF],
        "PRODUCT 1", CALCULATE (
            [Volume do Periodo],
            FILTER ( dProdutos, dProdutos[typeProduct] = "PRODUCT 1" && dProdutos[BRAND] = "BRAND X")
        ),
        "PRODUCT 2", CALCULATE (
            [Volume do Periodo],
            FILTER ( ALL ( dProdutos ), dProdutos[typeProduct] = "PRODUCT 2" )
        )
    )

[Ashish_Mathur]

Hi,

Share some data, explain the business context and show the expected result.