cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

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

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

1 ACCEPTED SOLUTION
Regular Visitor

I solved that problem by using summarize:)

3 REPLIES 3
Regular Visitor

I solved that problem by using summarize:)

Community Support

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.

``````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

Regular Visitor

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

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors