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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
zivhimmel
Resolver I
Resolver I

Remove filter

Hi,

I'm familiar with removefilter, all, filter and other functions that allow you to apply/remove filter.

However, I have a case where I'm not sure how to remove a filter, and this has to do with ignoring a slicer.

I have a slicer that controls a date range.

Then I have a table, where I show a record per territory, basically showing how much was sold in that territory for the date range selected in the slicer. 

However, I want to add another column, that shows sales this month or this day. This month or this day may be outside the date range selected, and this is where I'm struggling. I know how to create a measure that would show me sales in the last date selected, but not how to show me the sales if the date range selected is historic.

Basically I want to ignore the date slicer for this specific measure, but not ignore the context of the record (territory)

I hope I'm clear. 

Any ideas?

 

13 REPLIES 13
LS
Frequent Visitor

Did you ever found a solution for this?

I have the same issue and would like to hear about a solution if you have one ;o)

thanks in advance

edhans
Super User
Super User

You would need to use ALL(Date[Date]) or ALL('Date') for the entire table in that measure as a CALCULATE modifier which would not impact the Territory and other filters that the model generates.

 

If you need more help, we will need data and info.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Thanks @edhans.

I was thinking about using ALL.

But, when I use it, I still need to apply the filter to take the last period's sales, and this seems to still bring me back to the last period in the range selected and not the last period there is.

This is what I used. The ALL part is practically useless here:

 

Latest Period Sales =
CALCULATE(sum('Sales'[Sales Amount]),
ALL('Sales'[Period Ending]),
FILTER('Sales',
'Sales'[Period Ending]=max('Sales'[Period Ending])))

This will not work.

 

 

Latest Period Sales =
CALCULATE(
    SUM( 'Sales'[Sales Amount] ),
    ALL( 'Sales'[Period Ending] ),
    FILTER(
        'Sales',
        'Sales'[Period Ending]
            = MAX( 'Sales'[Period Ending] )
    )
)

 

The 'Sales'[Period Ending] and MAX(Sales[Period Ending]) are the same thig in that context.

 

You need to get the data before CALCULATE does its work, so probalby something like:

 

 

Latest Period Sales =
VAR MaxPeriod =
    MAX( 'Sales'[Period Ending] )
RETURN
    CALCULATE(
        SUM( 'Sales'[Sales Amount] ),
        ALL( 'Sales'[Period Ending] ),
        FILTER(
            'Sales',
            'Sales'[Period Ending] = MaxPeriod
        )
    )

 

 

Or use the variable to trap the period ending and use MAX within calculate. Again, not sure what you are doing as I don't have any data, expected output, or sampe info to work with. The VAR is like using EARLIER() to grab the context before CALCULATE() does its context transition, but EARLIER() has largely been depreciated since VAR came on the scene.

 

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Thanks again @edhans .

When you try to 'trap' the max period in the variable, it still takes into account the date range selected in the slicer, so it still doesn't change the outcome. Any other idea?

I can try and create a dummy dataset.

OK, here's a link to a folder with  a csv file with data, and a pbix file.

https://drive.google.com/drive/folders/1Z1APtxKLNIXttJITA3ZANo3PCrh2tVeu?usp=sharing

 

There's also a screenshot in that folder. For some reason I couldn't paste it here.

I want the 'last period sales' column to show april's sales and not March.

Thanks!

@edhans did you have a chance to look at the files I provided. I think you were very close to offering a solution, really appreciate your help!

No, sorry @zivhimmel . I thought someone else had jumped in. I'll download and take a look.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Hi @zivhimmel ,

 

Like this?

c1.PNG

Try to create a summary table.

Table = 
SUMMARIZE(
    Sales,
    Sales[period], Sales[territory],
    "sales",
    SUM(Sales[Sales Amount])
)

The columns in the summary willn't be filtered by the columns in the sales table.

 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks @v-lionel-msft for your time but I don't think you understood what I need.

Your solution is to create a summary table which isn't needed really, and you've completely changed the display table structure I provided.

I could create what you've created without an extra table, but again, this is not what I need.

Anyway thanks for your time.

 

Hi @zivhimmel ,

 

In the following case, what values do you want your measure to return or what do you want to display for the unfiltered table?

C5.PNG

 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

"Latest Period Sales" should diaply the latest period sales available in the dataset,

regardless of what's selected in the slicer. 

So for the dataset I provided, it shoud display 1200 for EU and 15000 for US.

In the example I provided, it uses latest period=1/3, and shows 2600 for EU and 20000 for US.

It does that because the measure is looking for the latest period and it is influenced by the slicer.

I want the measure to ignore the slicer and I can't figure out how to do so.

Thanks.

Hi @zivhimmel ,

 

Like this?

If  what you want is like this, you can only build another aggregation table:

 

Table = 
var x =
CALCULATE(
    MAX(Sales[period]),
    ALL(Sales),
    ALLEXCEPT(Sales, Sales[territory])
)
RETURN
SUMMARIZE(
    Sales,
    [territory],
    "Amount", 
    CALCULATE(
        SUM(Sales[Sales Amount]),
        FILTER(
            Sales,
            Sales[period] = x
        )
    )
)

 

 

C11.PNG

 

 

 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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