Forum Discussion
MAX function with FILTER and ALL
I have a situation where I would just like the actual field value shown in a measure. I still need to do a calculation, but do not know how to proceed with performing the calculation on just the value with no aggregation? I need it to be a measure because it is dynamic and based upon a user selecting an option from a slicer. I assumed since there was one value, I could use MIN or MAX and this works for my other measure that is providing the selected month field value but not for my previous month within the IF statement? What happens is that it is giving me the max result out of all the values for that month, not for each unique item row by row. What function would I use for row by row?
Here is my DAX function:
LastMonth =
VAR SelectedMonth = SELECTEDVALUE(Table[Month])
VAR LastMon = MONTH(EDATE(SelectedMonth, -1))
RETURN
IF(MONTH(EDATE(SelectedMonth, -1)) = LastMon,
CALCULATE( MAX(Table[Field]),
FILTER(ALL(Table), Table[Month] = LastMon)), "None")
Hi iLoatheMSDeep,
Thank you for reaching out to the Microsoft fabric community forum. I reproduced the scenario again, and it worked on my end. I used it as sample data and successfully implemented it.
outcome:
I am also including .pbix file for your better understanding, please have a look into it.Hope this clears it up. Let us know if you have any doubts regarding this. We will be happy to help.
Thank you for using the Microsoft Fabric Community Forum.
8 Replies
Hi,
I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.
Please try to create start schema with using dimension tables (calendar dimension, item dimension, ... ) and relating to the fact table.
Previous Month Min sales per date: = VAR _previousmonthend = EOMONTH ( MAX ( 'calendar'[Year-Month sort] ), -1 ) VAR _t = SUMMARIZECOLUMNS ( 'calendar'[Date], 'calendar'[Year-Month sort], 'calendar'[Year-Month], FILTER ( ALL ( 'calendar' ), 'calendar'[Year-Month sort] = _previousmonthend ), "@sales", SUM ( sales[sales] ) ) RETURN MINX ( _t, [@sales] )Hi, thanks for your reply. I'm not sure this helps me? I do have a Calendar table with the Date key linked to the Date field in my Data Table ( 1:many relationship with the Calendar [1]: Table[Date] many). In any case, my data is qualitative not quantitative, which is the issue I am having. I just want a way to show the Previous Month's data for a specific field on the same axis as my current month data for that field in a stacked bar chart. What is happening is that because I am doing a MAX calcuation is it is just showing the MAX of all the data of that field for the previous month on every item.
While I appreciate your table, I'm not sure it is synthesizing properly for me because a) I'm not using a table visual, b) I don't have Sales data, and c) I still don't know which function is appropriate to get the output I want. Here is what I need to do:
1.) Use the date slicer so that the user can select any month.
2.) The selected month data shows in the bar graph along with the data of the previous month.3.) This must be dynamic so the previous month is always based on the month the user selected.
In my current month measure that is filtering the data in the visual based on the Date slicer, I am using just a simple function: CALCULATE(MAX(Table[DataField])), and this gives me the value of DataField for each row; it does not find the MAX.
In my example above, I am getting the previous month's data but instead of giving me the DataField value for last month's entry for each item, it is actually giving me the MAX DataField value out of all the entries for the previous month and repeating that number everywhere.
My question, specifically, was how to remove the Date slicer filter from the previous month function and still have the actual field value [singular value, not the MAX] display in the bar graph for each item? Your function still seems to do a calculation, the SUM, and then finds the min over all the rows, which is not what I want. I do not want the data aggregated at all because they aren't quantitative numbers, they are descriptive numbers which represent a rating.Hi,
I have solved a similar question in the attached PBi file.
Hope this helps.
Hi iLoatheMSDeep,
Thank you for reaching out to the Microsoft Fabric Community Forum. Also, thanks to Jihwan_Kim, for his inputs on this thread. I reproduced the scenario, and it worked on my end. I used it as sample data and successfully implemented it.outcome:
I am also including .pbix file for your better understanding, please have a look into it.Hope this clears it up. Let us know if you have any doubts regarding this. We will be happy to help.
Thank you for using the Microsoft Fabric Community Forum.
Thanks, but this still doesn't fit the scenario I described. You have one field for previous month showing; I need to show the selected month data AND previous month at the same time. This means I have to remove the date slicer filter from the Previous Month measure, and then apply my previous month filter to the Date field in order to get the last months values. Again, the problem I am trying to solve is how to get the singular value for each row, and not the MAX for every item...
For your measures, my question would be what is the syntax for removing the filter and adding a new one within a LOOKUP function? Is that possible?Thanks
Hi iLoatheMSDeep,
Thank you for reaching out to the Microsoft fabric community forum. I reproduced the scenario again, and it worked on my end. I used it as sample data and successfully implemented it.
outcome:
I am also including .pbix file for your better understanding, please have a look into it.Hope this clears it up. Let us know if you have any doubts regarding this. We will be happy to help.
Thank you for using the Microsoft Fabric Community Forum.
