cancel
Showing results for
Did you mean:  Helper II

## need help to use together Calculate, filter and userelationship

HI I have two tables, (1) Data (2 ) Calender. Data table looks like below My Calender table has relation link with abc date, xyz date and def date

i need to get sum of abc value, xyz value, def value in date range. i.e. i need get sum for all in month Oct 2021.

for that i use below measure

abc value1 = CALCULATE(
SUM('Data'[abc value]),USERELATIONSHIP(Calender[Date],'Data'[abc date]))

if i use this
xyz value1 = CALCULATE(
SUM('Data'[xyz value]),USERELATIONSHIP(Calender[Date],'Data'[xyz date])) it show blank because its already filter on abc date.
i need to filter out and use "USERELATIONSHIP "

can anyone help me. I really appriciate your support and big thanks in advance

2 ACCEPTED SOLUTIONS  Community Support

Hi @vcm2778

I've changed my measure, you can try this,

1. create an independent calendar table 2. create the measures

``_selectedDateMax = CALCULATE(MAX(Calender[Date]),ALLSELECTED(Calender))``
``_selectedDateMin = CALCULATE(MIN(Calender[Date]),ALLSELECTED(Calender))``
``abcTotal = CALCULATE(SUM(Data[abc value]),FILTER(ALL(Data),Data[abc date]<=[_selectedDateMax]&&Data[abc date]>=[_selectedDateMin]&&Data[Section]=MIN(Data[Section])))``
``defTotal = CALCULATE(SUM(Data[def-value]),FILTER(ALL(Data),Data[def date]<=[_selectedDateMax]&&Data[def date]>=[_selectedDateMin]&&Data[Section]=MIN(Data[Section])))``
``xyzTotal = CALCULATE(SUM(Data[xyz-value]),FILTER(ALL(Data),Data[xyz date]<=[_selectedDateMax]&&Data[xyz date]>=[_selectedDateMin]&&Data[Section]=MIN(Data[Section])))``

result  Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.  Community Support

sample file attached bellow

Best Regards,

Community Support Team _Tang

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

10 REPLIES 10  Community Support

Hi @vcm2778

you can try this.

``````abcTotal =
var _selectedDate1=CALCULATE(MIN(Calender[Date]),ALLSELECTED(Calender))
var _selectedDate2=CALCULATE(MAX(Calender[Date]),ALLSELECTED(Calender))
return
CALCULATE(SUM(Data[abc value]),FILTER(ALL(Data),Data[abc date]<=_selectedDate2&&Data[abc date]>=_selectedDate1))``````
``````defTotal =
var _selectedDate1=CALCULATE(MIN(Calender[Date]),ALLSELECTED(Calender))
var _selectedDate2=CALCULATE(MAX(Calender[Date]),ALLSELECTED(Calender))
return
CALCULATE(SUM(Data[def-value]),FILTER(ALL(Data),Data[def date]<=_selectedDate2&&Data[def date]>=_selectedDate1))``````
``````xyzTotal =
var _selectedDate1=CALCULATE(MIN(Calender[Date]),ALLSELECTED(Calender))
var _selectedDate2=CALCULATE(MAX(Calender[Date]),ALLSELECTED(Calender))
return
CALCULATE(SUM(Data[xyz-value]),FILTER(ALL(Data),Data[xyz date]<=_selectedDate2&&Data[xyz date]>=_selectedDate1))``````

result Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.  Helper II

HI Mate,

I really appriciate your  help. I checked your file but if i add 1 additional column like below and I use matrix and section values goes to Rows then it shows all static values in matrix, it shows like below and i need to use month instead of dates in slicer. i need to show those values in matrix only. I hope i could explain better.

Heaps thanks  Community Support

Hi @vcm2778

the solution I provided was based on the original question you posted. I'll changed my measure based on the new question. but before the adjustment, I need to confirm this,

`` i need to use month instead of dates in slicer``

do you mean you need this type of slicer? Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.  Helper II

HI Mate, thanks for your quick reply. and sorry i amended my original question.

i need slicer like below like this Thanks  Community Support

Hi @vcm2778

I've changed my measure, you can try this,

1. create an independent calendar table 2. create the measures

``_selectedDateMax = CALCULATE(MAX(Calender[Date]),ALLSELECTED(Calender))``
``_selectedDateMin = CALCULATE(MIN(Calender[Date]),ALLSELECTED(Calender))``
``abcTotal = CALCULATE(SUM(Data[abc value]),FILTER(ALL(Data),Data[abc date]<=[_selectedDateMax]&&Data[abc date]>=[_selectedDateMin]&&Data[Section]=MIN(Data[Section])))``
``defTotal = CALCULATE(SUM(Data[def-value]),FILTER(ALL(Data),Data[def date]<=[_selectedDateMax]&&Data[def date]>=[_selectedDateMin]&&Data[Section]=MIN(Data[Section])))``
``xyzTotal = CALCULATE(SUM(Data[xyz-value]),FILTER(ALL(Data),Data[xyz date]<=[_selectedDateMax]&&Data[xyz date]>=[_selectedDateMin]&&Data[Section]=MIN(Data[Section])))``

result  Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.  Community Support

sample file attached bellow

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.  Helper II

Hi Mate, I really appriciate your help and time. all good , I must say you are genious

Heaps thansk  Super User

Hey @vcm2778 ,

I'm pretty sure that I miss something about the the structure of your data table, but from a first glance I would recommend to reshape the data that the table will look like this

date | type | value

abc-date | abc | value

xyz-date | xyz | value

then you can connect to your date table to the date column and you don't have to struggle with always activating the proper relationship.

Nevertheless, use CROSSFILTER (https://dax.guide/crossfilter/) to deactivate the relationship in addition to USERELATIONSHIP.

Hopefully, this provides what you are looking for.

Regards,

Tom

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany  Helper II

Hi Tom

I have been wasting whole day to figure out the issue but i could not. can you please help. thanks  Helper II

HI Tom.

Thanks for your reply. as far as reshape is concern then those 6 columns in my above image is part of 75 columns in excel file. how can I reshape only those 6 coulmns.

and you mentioned me CROSSFILTER another option. would you mind to show / updating my existing meassure.

Very honest i dont want to disturb that entire sheet by rephasing. if this is the only option then let me know

Thanks   