filters and dax functions

[smatesic]

I need to connect two different tables and I use RELATEDTABLE and CALCULATETABLE functions. They work fine, I get aditional columns - columnA and columnB which contain data that was calculated using these two functions. On my 'canvas' I select table or matrix visualisation, chose, for example, Date / StartTime / EndTime / Employee / ColumnA / ColumnB to show in my visualisation and all data is correct. But when I add another visualisation - this time slicer by Year - and select, lets say 2015th, data in columnA and columnB is not filtered. Filters don't apply to columnA and columnB.    

Do I use other functions, in that case which ones? 

Hope I was clear enough

[Greg_Deckler]

CACULATETABLE removes all filter contexts and creates its own. From the documentation: https://msdn.microsoft.com/en-us/library/ee634760.aspx

"The CALCULATETABLE function changes the context in which the data is filtered, and evaluates the expression in the new context that you specify. For each column used in a filter argument, any existing filters on that column are removed, and the filter used in the filter argument is applied instead."

This is likely what you are running into.

To solve your issue, I'd probably need some sample data and your formulas. There is likely an alternative solution that does not include CALCULATETABLE.

[smatesic]

I understand CALCULATETABLE function. What about RELATEDTABLE? 

We need „Power BI Page Filter context“ to be applied on RELATEDTABLE function. How to accomplish this?

We do not need to create new context or new / additonal filters. Just use existing Page filters from other elements inside Report Page

[Greg_Deckler]

You're in the same boat, from the documentation on RELATEDTABLE:

https://msdn.microsoft.com/en-us/library/ee634226.aspx

"The RELATEDTETABLE function changes the context in which the data is filtered, and evaluates the expression in the new context that you specify.

This function is a shortcut for CALCULATETABLE function with no logical expression."

You could perhaps use "RELATED" but without having some semblance of your data and what you are trying to accomplish with it, I'm not sure how much useful advice I can provide. I hate having to only say "you can't do it that way" without actually providing some sort of useful advice, but there it is I guess...

[greggyb]

CALCULATE() and CALCULATETABLE() do not remove all context.

They first transform the current row context into filter context, grab all filter context external to the function and internalize that, all of these combined into a single filter context (all conditions combined in a logical and) for the expression in the first argument.

If you use no filter arguments, then this is the extent of what CALCULATE() and CALCULATETABLE() do.

If you add filter arguments, these can remove, add, or modify existing context (all that which was internalized as described above).

If you use a simple predicate, e.g.

CALCULATE(
    [Measure]
    ,DimDate[Year] = 2015
)

If a filter context already exists on DimDate[Year], this will be cleared and replaced. If no filter context exists on DimDate[Year], then this will be combined in a logical and with the rest of the filter context.

CALCULATE(
    CALCULATE(
        [Measure]
        ,DimDate[Year] = 2015
    )
    ,DimDate[Year] = 2016
)

The internal CALCULATE() first internalizes the context on [Year] = 2016. It then evaluates its own filter argument and replaces the context on [Year] to be [Year] = 2015.

The above will only return [Measure] values for [Year] = 2015.

CALCULATE(
    CALCULATE(
        [Measure]
        ,DimDate[Year] = 2015
    )
    ,DimDate[MonthNumber] = 1
)

This would give us [Measure] for only January, 2015.