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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
MDB
Regular Visitor

Slicing by the column im removing from a measure with ALL or REMOVE, changes said measure.

Good day. 

 

I have the following measures which are supposed to count the amount of voyages a particular operator has done on a specific trade and timeframe. Also, I have a second measure, where I remove the operator column using ALL or REMOVEFILTERS, which is supposed to give me the total amount of voyages so that I can calculate the market share for each individual operator.

 

Voyages = CALCULATE(COUNT(Voyages[Voyage ID]))
Total Voyages = CALCULATE([Voyages],REMOVEFILTERS(Voyages[Operator])

 

PBI A.PNG

 

So far so good, everything is working as it should.

 

The problem starts when I try to slice the visual using the same Operator column I'm using REMOVEFILTERS on (I have several operators, and would only like to see the top/bottom ones for example, but I dont want the market share to change). I would have hoped that the Total Voyages column remains unchanged at 23, but for some reason it is showing 5, and I cant understand where this is coming from.

 

PBI B.PNG

 

So I'm guessing there might be a basic thing here I'm completely overlooking. Does anyone know what this might be?

 

Sorry but I can't attach a sample file, hopefully I made myself clear enough. Thanks! 

5 REPLIES 5
tamerj1
Super User
Super User

Hi @MDB 

I'm really curious to understand where 5 comes from. Can you please share the sample data?

MDB
Regular Visitor

Hi @tamerj1 

 

Thank you for replying. I've created a simplified sample which replicates my model, which you can find attached below. I also believe to have found what is causing this issue, although I have no idea why. 

 

I thought the issue might show up because of the interaction with other slicers. I have several of them on my visualizations page which I use to fiter through my data, but for this particular sample I'm showing just 3 of them: Operator, Direction and Loading Month.

 

So lets say I want to look at just the voyages on direction 2 (which is equivalent to what i showed in my original post). As you can see, REMOVEFILTERS works just fine in this case, even when slicing for the Operator. Total voyages remains constant at 23.

MDB_0-1696016218714.png

MDB_1-1696016368075.png

The other slicer is the loading date, and this is what has been causing the problem all along. Once I start filtering by date, REMOVEFILTERS stops working properly. It now shows 21 instead of 23, and if you select B and E you'll get 6.

 

MDB_2-1696016505707.png

So it's definitely related to this date column, altough I still don't know why. At least I can work around it now, but any suggestions will be appreciated.

 

Thanks!

 

https://we.tl/t-pezmYl5gkD 



@MDB 
now it makes sense. You are removing the filter from one column but keeping the filter from others. You may try

Total Voyages =
COUNTROWS ( ALL ( Voyages[Voyage ID] ) )

MDB
Regular Visitor

Hi @tamerj1 

 

Yes, my intention is to remove the filter from the operator column, since I want to filter the rows on my visual (table)  withouth affecting the calculations. 

 

So for example, operator "A" performed 19 voyages of a total of 23 on direction "2". So far so good. But once I filter by another column (date in this example), the total calculation drops to 21.

 

COUNTROWS(ALL)) would not work in this particular case, since it would give me all rows in the table, and I've got several slicers that need to be applied to the data. 

@MDB 

You can remove all filters and the ones you need manually using VALUES

=
COUNTROWS (
CALCULATETABLE (
'Table',
ALL ( 'Table' ),
VALUES ( 'Table'[Column1] ),
VALUES ( 'Table'[Column2] ),
VALUES ( 'Table'[Column3] )
)
)

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors