cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Mode calculation with filter in CALCULATE

Hi guys,

Need your help again. I'm still stuck on MODE (most probable value, MVP) calculations. I've added a filter to the CALCULATE-function in the code and it looks like this:

 

Date MPV = 
MAXX (
    TOPN (
        1;
        ADDCOLUMNS (
            VALUES ( 'Table'[Numbers] );
            "Frequency"; 
            CALCULATE ( 
                COUNT ( 'Table'[Numbers] );
                FILTER(
                    VALUES('Table'[Date]);
                    'Table'[Date]=DATE(2016;11;03)
                )
            )
        );
        [Frequency];
        0
    );
    'Table'[Numbers]
)

My understanding of contexts here is that if I create a visual table with 'Table'[Date] in the first column and my [Date MVP] measure in the second column (See picture below) this should generate a table with blank entries for all dates except for 2016-11-03. Howerver, as also can be seen in the same picture below, this is not the case. There are different data values for each date-row. Although the Total at the bottom does correspond to the date set in the Filter-function inside Calculate (if I change the date in the filter-function inside Calculate the total changes to the corresponding dates value).

 

DateMVP.PNG 

 

Why is this? As far as I can tell somewhere outside the calculate function somthing is changing the context so it ends up printing the daily value even though I don't want it to. Where does this happen?

 

This is screwing with my analysis since I actually want to find the MVP for a month and have that one value displayed for all dates in the corresponding month in a table as the one above.

 

Any suggestions are more than welcome!

 

Cheers,

 

OscLar

2 REPLIES 2
Highlighted
Microsoft
Microsoft

Re: Mode calculation with filter in CALCULATE

@OscLar,

The above measure is calculated according to context(query context, row context), if you want to get the number for the 2016-11-03, add if condition in your DAX.

Date MPV = 
IF(MAX('Table'[Date])=date(2016,1,3),MAXX (
    TOPN (
        1;
        ADDCOLUMNS (
            VALUES ( 'Table'[Numbers] );
            "Frequency"; 
            CALCULATE ( 
                COUNT ( 'Table'[Numbers] );
                FILTER(
                    VALUES('Table'[Date]);
                    'Table'[Date]=DATE(2016;11;03)
                )
            )
        );
        [Frequency];
        0
    );
    'Table'[Numbers]
),BLANK())


About context, please review the following blogs.
https://www.sqlbi.com/articles/row-context-and-filter-context-in-dax/
http://www.decisivedata.net/blog/understanding-row-context-nested-functions-earlier-powerpivot

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper I
Helper I

Re: Mode calculation with filter in CALCULATE

Hi Lydia,

 

Thanks for your reply. However, to me this seems like more of a work around. If You add the IF-statement like you do, then you don't need the FILTER-argument inside the CALCULATE function. And while this does what I tried to accomplish, it doesn't make me understand how the MODE-calculation works with context.

 

What I want to learn is how to change the context in the mode calculation. As I mentioned I would like to have the mode calculated for each month and then havinge each date in the corresponding month showing that number.

 

Cheers,

 

OscLar

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors