Forum Discussion

michael_ma's avatar
michael_ma
Frequent Visitor
2 years ago

All except function not working as expected

Hi Everyone, 

I'm currently trying to migrate over a report from Tableau to Power BI but having a bit of trouble understanding what's going on with the All Except function. 

 

This is my desired result taken from Tableau :

However, when I try and reproduce the visual in Power BI, this is the result I'm getting:

My measure is for Royal Moore Invoice Net Amt is as follows: 

The customers and transactions tables have a 1: many relationship. I'm guessing what's happening here is that the all except function is trying to filter the customer table, but doesn't capture the data in the transactions table. I'm sure that if I create a denormalized view of the transactions and customer table I can achieve the desired result, but that doesn't seem like a long term practical solution. I'm wondreing if anyone has any other idea where I can keep the star schema structure of my data model intact, but still achieve my desired result. 

3 Replies

  • sevenhills's avatar
    sevenhills
    Super User
    2 years ago

    Try this and see if it works

     

    Royal Moore Invoice Net Amt =
CALCULATE (
    SUM ( Transactions[INVOICE_NET_AMOUNT] * -1 ),
    ALLSELECTED ( Transactions ),
    VALUES ( Transactions[Top ... Number] )
)
    • michael_ma's avatar
      michael_ma
      Frequent Visitor
      2 years ago

      The problem is that the top level parent customer number field comes from the customer table and not the transactions table. In order to make your solution work I would have to add top level parent customer number field to the transactions table. I'm sure if I do that your soultion will work, but adding a text field to my fact table doesn't seem like a good idea

      • sevenhills's avatar
        sevenhills
        Super User
        2 years ago

        My bad! I missed that one! Try this!

        Measure DAX

         

        Royal Moore Invoice Net Amt =
CALCULATE (
SUM ( Transactions[INVOICE_NET_AMOUNT] * -1 ),
ALLSELECTED ( Transactions ),
VALUES ( Customers[Top ... Number] )
)

         

         

        You can write also as

         

        CALCULATE(
        SUM('Transactions'[INVOICE_NET_AMOUNT] * -1),
        ALLSELECTED(Transactions),
        Filter(
        all('Customers'),
        Customers[Top ... Number] = SELECTEDVALUE(Customers[Top ... Number])
        )
        )

         

        and for line sum with other columns, you can try as 

         


        CALCULATE(
        SUM('Transactions'[INVOICE_NET_AMOUNT] * -1),
        Filter( all('Customers'),
        Customers[Top ... Number] = SELECTEDVALUE(Customers[Top ... Number])
        )
        )

         

        Hope it helps!