Forum Discussion

OscLar's avatar
OscLar
Icon for Helper I rankHelper 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 = 
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).

 

 

 

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

Need Help
Tips and Tricks

2 Replies

  • 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
      Icon for Helper I rankHelper 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