topic Re: How to get distinct value over overlaping period while filtering on one year in DAX Commands and Tips

How to get distinct value over overlaping period while filtering on one year

KXuan — Wed, 31 Jul 2024 20:50:51 GMT

Hello everyone,

I need you help, I've been trying to solve this problem for several days and still don't know what to do.

I have a contract with 2 rows in my fact table, the first line is when the contract beginns and the second line is the extension. Both have the same price of 17,99Euro. According to bussiness definition , if I filter on 2019, I need to get the price 17,99 once in total, because the price should be considered only once in one year (2019).

And if I do not filter, then I should get the sume of those, since I'm querying the entire period. In this case, my measure works. Has anyone faced this problem, or has idea how to solve this with dax ?

Here is my measure :

var minDate = [minSelectedDate (Date)]

var maxDate = [maxSelectedDate (Date)]

return

CALCULATE(sum( '(F) Contract'[Price])

, '(F) Contract'[Begin]<= maxDate && '(F) Contract'[End] >=minDate

)

Thanks,

Xuan

Re: How to get distinct value over overlaping period while filtering on one year

Anonymous — Thu, 01 Aug 2024 03:18:20 GMT

Hi KXuan ,

Do you want to filter the table when year is selected in slicer? For example, when user selects 2019, the first row will be filtered.

Please modify your original Dax expression as following.

Measure = var minyear= MIN('(F) Contract' [Beigin]) var maxyear= MAX('(F) Contract' [End]) RETURN IF(SELECTEDVALUE(Table2[Year]) <> BLANK() , IF(SELECTEDVALUE(Table2[Year]) > YEAR(minyear) && SELECTEDVALUE(Table2[Year]) <= YEAR(maxyear) , 1 , 0) , 3)

Then set Filter as following.

Here is my test for your reference.

Best regards,

Mengmeng Li

Re: How to get distinct value over overlaping period while filtering on one year

KXuan — Thu, 01 Aug 2024 07:36:20 GMT

Hello Mengmeng ,

Unfortunately, it's not what I want :(. Actually ,I just want to have the correct total without filtering in the table as in you example.

The idea of this measure is to get the price of contract which is valid in selected period ( so I think _maxyear und _minyear should be based on seperate date dimension instead ). In this example, it's 17,99 for 2019 or 2020 and 35,98 for the entire period.

Hope, that I explaned it well 😞

Best,

Xuan

Re: How to get distinct value over overlaping period while filtering on one year

KXuan — Wed, 14 Aug 2024 14:41:47 GMT

I solved that problem by using summarize:)