Ignore slicer for measures but not columns

[Anonymous]

Hi all!

I am struggling with displaying HR information and salary differences. I have a dashboard that needs to show column data and measure data, sliced per year. Furthermore, I have an additional calendar table, this is used for the slicer.

If I relate the dates in my data table to the calendar, the visuals of the columns work fine, but my measures do not. If I remove this relationship, the visuals of the measures work fine, but the columns are now not influenced by the slicer anymore.

What I want to achieve with my measure, is finding the status (and salary and career) someone had at the start of the year. Therefore, I need to look at the max date before the start of the year chosen in the slicer.

M-Start Status = 
var CurrName = LASTNONBLANK('Mutations_tab'[Name],[Name])
return

var LastStatusDate = 
CALCULATE(
    MAX('Mutations_tab'[Date]),
    FILTER(
        'Mutations_tab',
        'Mutations_tab'[Date] <= MIN('Calendar'[Date]) &&
        'Mutations_tab'[Name] = CurrName &&
        'Mutations_tab'[Status] <> BLANK())
)
return

LOOKUPVALUE(
    'Mutations_tab'[Status],
    'Mutations_tab'[Name],
    CurrName,
    'Mutations_tab'[Date],
    LastStatusDate
)

The mutation table looks like this:

Name	Date	Salary	Career	Status
B	1-1-2021	€ 3.000,00	Medior
C	1-1-2021	€ 3.500,00	Senior
A	1-1-2021			NO PROJECT
D	2-12-2020			LEAVING
A	30-6-2020			ON PROJECT
A	29-6-2020	€ 2.750,00	Junior	ENTERING
B	5-5-2020			ON PROJECT
B	4-5-2020	€ 2.750,00	Junior	ENTERING
D	3-1-2020			ON PROJECT
D	2-1-2020	€ 2.750,00	Junior	ENTERING
C	1-1-2020	€ 3.000,00	Medior
C	2-8-2018			ON PROJECT
C	1-8-2018	€ 2.750,00	Junior	ENTERING

And I have an additional table with other information about the employees, such as the studies.

If I relate calendar to the table, the graphs that only use columns work fine but the measures don't. If I remove this relationship, the measures work fine but the columns don't. Please look at my pbix file, thanks for your help!

Click here for my PBIX file:

https://1drv.ms/u/s!AiMbWK0OjrARgtBLS50Bp5ZYAnX6kQ?e=RAkYE0

[v-eqin-msft]

Hi @Anonymous ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it.

Best Regards,
Eyelyn Qin

[v-eqin-msft]

Hi @Anonymous ,

According to my understanding, you want all filter arguments in your existing CALCULATE are evaluated with the relationship disabled, right?

You could wrap your existing code in an outer CALCULATE with CROSSFILTER.And the logic of CurrName seems to be wrong, please try the following formula:

M-Start Status = 
var CurrName =CALCULATE(MAX('Mutations_tab'[Name]),FILTER(ALL('Mutations_tab'),'Mutations_tab'[Date]<=MIN('Calendar'[Date])))
return

var LastStatusDate = 
CALCULATE(
CALCULATE(
    MAX('Mutations_tab'[Date]),
    FILTER(
        'Mutations_tab',
        'Mutations_tab'[Date] <= MIN('Calendar'[Date]) &&
        'Mutations_tab'[Name] = CurrName &&
        'Mutations_tab'[Status] <> BLANK())
), CROSSFILTER('Calendar'[Date],Mutations_tab[Date],None))

return

LOOKUPVALUE(
    'Mutations_tab'[Status],
    'Mutations_tab'[Name],
    CurrName,
    'Mutations_tab'[Date],
    LastStatusDate
)

 The final output is shown below:

Please take a look at the pbix file here.

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks for your help! I see how and that it works in your pbix, but unfortunately it doesn't work in my 'real' pbix and I really don't know why, everything has the same set-up.... But since the data is of course confidential I cannot share it 😕 Hopefully I can find out the difference and how to fix it, but again thanks for your help!

[v-eqin-msft]

Hi @Anonymous ,

It may be caused by version.Please try to upgrade the Power BI Desktop to the latest version and have a try again.

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Sorry for my late reply! I've updated my Power BI version, but unfortunately it still doesn't work 😞 

[Anonymous]

I've already tried some things with ALL, ALLEXCEPT and CROSSFILTER in the measure while keeping the relationship activated, but so far nothing works....