Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
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] )
)
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
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!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
24 | |
21 | |
18 | |
14 | |
11 |
User | Count |
---|---|
43 | |
35 | |
25 | |
24 | |
22 |