cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
OscLar
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
v-yuezhe-msft
Microsoft
Microsoft

@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.

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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.