CODEWITHSUNDEEP
powerbidax
Frederi ROSE
Frederi ROSE

Reputation: 351

Power BI - Calculation group does not produce the same output as measure

In the following Power BI file, I have defined a calculation group 'CG - Last days'[Name] = "5 last days" as follow :

VAR tbl_FILTER =
        TOPN(
            10
        ,   dim_DATES
        ,   dim_DATES[Date]
        ,   DESC
        )
RETURN
    CALCULATE(
        SELECTEDMEASURE()
    ,   KEEPFILTERS( tbl_FILTER )
    )

My problem is it does not produce the expected output : filtering on the 10 most recent dates of a date table with only January and February should return a count of 10 dates only for the month of February ; but that is not what I get :
enter image description here
If anyone knows why, I am all ears.

Upvotes: 1

Views: 626

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

One problem here is that calculation items don't modify just any DAX expression, only measure references. Try defining a count measure COUNT( dim_DATES_2[dat_DATE] ) and using that in your test instead.

Please see this article: https://www.sqlbi.com/articles/understanding-calculation-groups/

In particular, this part

The application of a calculation item replaces a measure reference with the expression of the calculation item, still applying an implicit context transition. Focus your attention on this sentence: A measure reference is replaced. Without a measure reference, a calculation item does not apply any modification. For example, the following code is not affected by any calculation item because it does not contain any measure reference:

CALCULATE (
   SUMX ( Sales, Sales[Quantity] * Sales[Net Price] ),
   'Time Intelligence'[Time calc] = "YTD"
)

Edit: As mentioned in the comments, you also need to use ALL(dim_DATES) instead of dim_Dates inside the TOPN. This is because the dim_Dates has filter context applied to it from the visual, so only includes February dates in the February row and Januari dates in the Januari row. ALL removes this filter context and returns the top dates from the unfiltered dates table.

Upvotes: 1

Related Questions