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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.