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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Anonymous
Not applicable

DAX - Row cancellation based on selected attributes

Hello,


This is my first time writing on this forum. But it helped me many times before. I think I spend more time here than on any other website  🙂  ! What a great community!

For this issue I'm facing, after many searches on google, the community and stuff I tried on my own, I couldn't find any solution. So I'm sharing a topic in hopes of finding an answer.

We have an SSAS Tabular cube representing our datawarehouse.
We have a business requirement to cancel internal company transactions, so transactions between managers inside the company, whenever more than one manager is selected.

So in other words, we have a transaction table (fact), with different types of transactions (External Revenue from clients, structure cost, internal revenue from other managers, employee costs...etc).
We have another table called BU (business unit) where the business unit is the managers of managers of the employees.

The joins between BU and fact is made through both the employee and their manager, and then we create a parent child hierarchy to represent the organizational hierarchy in the BU table.

So when the BU hierarchy is used as a slicer or on the rows in Power BI, here are the scenarios needed:

1) If we select any filter that leaves us with more than one manager or if we filter on specific managers, we want to cancel all internal transactions between these selected managers.

2) If we select only one manager or if we apply a filter that leaves us with only one manager, we want to show all transactions, even internal one that he did with managers from the company.

3) If we select multiple managers or we apply a filter that leaves us with multiple managers, the internal transactions between these displayed managers and managers that aren't displayed need to be shown.

So we only filter internal transactions between the two or more displayed managers.

In our fact table, we have for every row, a column which specifies if the current transaction is an internal one.
If it is, the column value would be the second manager id with whom the transaction happened. Let's call this column "InternalTransactionWithID"

The closest thing I tried until now is the following DAX:

1) I created a flag to be used as a multiplier with the main measure: 

 

 

isInternalTransaction:= 

//This first variable gives the list of managers after application of all filters: 
VAR selectedManagers = CALCULATETABLE(BU, ALLEXCEPT(BU, BU[ManagerId]), ALLSELECTED(FactTable)) 

//The second variable provides a distinct list of the managers from the first variable: 
VAR listOfManagers = DISTINCT(SUMMARIZE(selectedManagers, [ManagerId])) 

//And a result variable that will check if on the current row, the InternalTransactionWithID is in the list of managers or not: 
VAR result = 
IF( COUNTROWS(listOfManagers)>1 //If More than one manager is selected 
&& COUNTX( FILTER( FactTable, FactTable[InternalTransactionWithID] IN SUMMARIZE(listOfManagers, [ManagerId]) ), 1 )>0, 0, //If condition is satisfied than 0 1 //else 1 ) 

RETURN 
result 

 

 


I then use isInternalTransaction in my main measure as follows:

 

 

Amount := 
SUMX(FactTable, FactTable[AmountColumn]*isInternalTransaction) 

 

 


Now this actually captures the internal transactions perfectly and the cancel them.
The only issue with it is that it also cancels the totals and set them to 0 on the parent level as soon as we have an internal transaction in the level under it.

So my qestion is this: Is there any possibility to apply this measure to only the last shown column in matrix rows knowing that the column to be used is unknown?

I'm also open to any other ideas that would answer my current issue.  Any help is very much appreciated as unfortunately this is currently blocking the Go live of the cube.

2 REPLIES 2
wdx223_Daniel
Super User
Super User

@Anonymous try to use ISFILTERED to judge total rows?

Anonymous
Not applicable

Hello Daniel,
The problem with ISFILTERED is that I have to specify all fields to be used.
Since the cube will be accessible via Excel pivot table, we won't be able to know which field are going to be used. Is there a way to dynamically specify the last used field in a visual?

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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