Forum Discussion

iLoatheMSDeep's avatar
iLoatheMSDeep
Regular Visitor
9 months ago
Solved

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 val...
Need Help
  • v-kpoloju-msft's avatar
    v-kpoloju-msft
    9 months ago

    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.

Jihwan_Kim's avatar
Jihwan_Kim
Super User
9 months ago

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] )

 

  • iLoatheMSDeep's avatar
    iLoatheMSDeep
    Regular Visitor
    9 months ago

    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.