Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
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
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
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThanks @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:
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
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThanks 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @zivhimmel ,
Like this?
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?
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
)
)
)
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.
User | Count |
---|---|
128 | |
112 | |
99 | |
65 | |
62 |
User | Count |
---|---|
138 | |
116 | |
102 | |
70 | |
57 |