Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

Reply
michael_ma
Frequent Visitor

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 :

michael_ma_0-1695074983367.png

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

michael_ma_1-1695075331212.png

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

michael_ma_2-1695075409288.png

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 3
sevenhills
Super User
Super User

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!

 

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.