Forum Discussion

OscLar's avatar
OscLar
Helper I
8 years ago

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 = MA...
Need Help
Tips and Tricks
Anonymous's avatar
Anonymous
Not applicable
8 years ago

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

OscLar's avatar
OscLar
Helper I
8 years ago

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