Forum Discussion

em6jq65's avatar
em6jq65
Frequent Visitor
8 years ago

Measure that ignores certain slicer

Hello, guys! I'm new to dax, but that's what I want to do:

I want to compare values from actual month's sales vs trend line. 

The 1st problem was to get values from trend line. ok, I found formula for measure on this forum. Here it is in my model:

 

VT_TrendLine = 
VAR Known =
    FILTER (
        SELECTCOLUMNS (
            CALCULATETABLE ( VALUES ( 'all_in_local'[Date] ), ALL ('all_in_local') ),
            "Known[X]", ('all_in_local'[Date]),
            "Known[Y]", [F_Sales_Value]
        ),
        AND ( NOT ( ISBLANK ( Known[X] ) ), NOT ( ISBLANK ( Known[Y] ) ) )
    )
VAR Count_Items =
    COUNTROWS ( Known )
VAR Sum_X =
    SUMX ( Known, Known[X] )
VAR Sum_X2 =
    SUMX ( Known, Known[X] ^ 2 )
VAR Sum_Y =
    SUMX ( Known, Known[Y] )
VAR Sum_XY =
    SUMX ( Known, Known[X] * Known[Y] )
VAR Average_X =
    AVERAGEX ( Known, Known[X] )
VAR Average_Y =
    AVERAGEX ( Known, Known[Y] )
VAR Slope =
    DIVIDE (
        Count_Items * Sum_XY - Sum_X * Sum_Y,
        Count_Items * Sum_X2 - Sum_X ^ 2
    )
VAR Intercept = Average_Y
    - Slope * Average_X
RETURN
    SUMX ( DISTINCT ( 'all_in_local'[Date] ),
    Intercept + Slope * 'all_in_local'[Date]
)

Ok, that's awesome! Now I have a trend line, that fits PowerBI's trend line and has actual values. But there is the problem. I cant get the last value from it because it uses dates in calculations and I have a date Slicer in my report. So as the slicer is used - all trend line data becomes invalid. How can I freeze dates in this calculation? The thing is, I have a few slicers on my page, and i need to moke it ignore only one - date one. Thank you, guys! 

1 Reply

  • Have you tried enhancing your measure with the FILTER function using ALL?  For example...

     

    ... RETURN

       CALCULATE( <your expression>,

          FILTER( ALL('all_in_local'), <any additional filters> ))