cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vcm2778
Helper II
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

vcm2778_0-1629895469232.png

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

Hi @vcm2778 

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

1. create an independent calendar table

vxiaotang_1-1630392678091.png

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

vxiaotang_0-1630392655283.png

vxiaotang_2-1630392709999.png

 

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.

View solution in original post

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.

View solution in original post

10 REPLIES 10
v-xiaotang
Community Support
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

vxiaotang_0-1630059285935.png

 

 

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.

HI Mate, 

I really appriciate your  help. I checked your file but if i add 1 additional column like below

 

vcm2778_0-1630300621174.png

and I use matrix and section values goes to Rows then it shows all static values in matrix, it shows like below

vcm2778_2-1630301785131.png

 

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.

Please let me know

Heaps thanks

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?

vxiaotang_0-1630318106000.png

 

 

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.

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

 

i need slicer like below 

vcm2778_0-1630323042192.png

 like this

 

vcm2778_1-1630323424928.png

 

 

 

Thanks

Hi @vcm2778 

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

1. create an independent calendar table

vxiaotang_1-1630392678091.png

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

vxiaotang_0-1630392655283.png

vxiaotang_2-1630392709999.png

 

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.

View solution in original post

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.

View solution in original post

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

Heaps thansk

 

TomMartens
Super User
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

Hi Tom

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

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

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.