Remove relationship filter in measure

[ArielK]

Hello Experts

I have report where I should show KPI value for department but also avg for the whole company.

For the company value I wrote measure that worked ok with any slicers in the report:

CALCULATE(

    AVERAGE(KOM_WSP[Wartość]),

    ALLEXCEPT('KOM_WSP',

        KOM_WSP[Atrybut.1],

        KOM_WSP[Atrybut.2],

        KOM_WSP[ROK])

)

After that I added RLS table ('RLS') that filters main table ('KOM_WSP') on the department. Since that, company KPI shows the same value as department one (what is obvious as the 'KOM_WSP' is filtered by 'RLS'), and unfortunately any approach to solve it didn’t work.

I tried variant with CROSSFILTER defined as “NONE” , as below:

CALCULATE(

    AVERAGE(KOM_WSP[Wartość]),

    CROSSFILTER(RLS[ALIAS], KOM_WSP[DEP_Alias], none),

    ALLEXCEPT('KOM_WSP',

        KOM_WSP[Atrybut.1],

        KOM_WSP[Atrybut.2],

        KOM_WSP[ROK])

)

I tried variant with ALL and REMOVEFILTERS on RLS table:

CALCULATE(

    AVERAGE(KOM_WSP[Wartość]),

    ALL(‘RLS’),

    ALLEXCEPT('KOM_WSP',

        KOM_WSP[Atrybut.1],

        KOM_WSP[Atrybut.2],

        KOM_WSP[ROK])

)

I also tried different structures and variants of those functions, also tried to nest one calculate in another for each filter argument.

Still the same problem, company value = department value. And now I runned out of ideas.

I would appreciate any suggestion.

[ArielK · Accepted solution]

Ok, problem solved / unsolved. 

Problem comes from the nature of RLS, as according to MS manual, RLS doesn’t work as regular relationship. It comes first, before dax, and cut-off data before anything else, so for the report data "doesn't exist", and crossfilter function with "none" parameter doesn't change it (it removes relationship, but the data is not there).

To overcome it I just made new, unfiltered table as a source of company data.

[ArielK]

Unfortunately I can't provide you with data, but structure of this part of report looks like that:

Relation looks like that (the green one has to be deactivated in the measure):

KOM_WSP table:

and RLS tabel like this:

[Anonymous]

Hi @ArielK ,

Can you provide some test data and related screenshots as reference, so that I can answer your question as soon as possible.

Best Regards,
Adamk Kong