cancel
Showing results for
Did you mean:
Frequent Visitor

## Dax İteration İssue

Hello everybody,

I have one MinSelectedDate as measure, and ı would like to find and return interest value which is between Start Date And Finish Date for that certain ID. And if interest value is equal to 0(zero) then it is going to find and return max İnterest value for that certain ID.

I tried to demonstrate an sample below:

 Table 1 ID interest value StartDate EndDate MinSelectedDate(measure) 1000000000007 10 15.2.2021 16.5.2021 31.3.2021 1000000000007 8 16.5.2021 17.6.2021 1000000000007 6 17.6.2021 15.8.2021 1000000000007 15 15.8.2021 12.11.2023 1000000000047 0 8.3.2021 7.5.2021 1000000000047 0.24 7.5.2021 15.8.2021 1000000000047 0.89 15.8.2021 14.5.2024 İf selected date is equal to 31.03.2021 then interest value will be equal to 10 for ID "1000000000007".And interest value will be equal to  0.89 for ID "1000000000047" because it is equal to zero(0) it will return max value for that certain ID VALUE.

How to solve that issue by creating calculated field?

İf somebody help ı will be very appreciated.

1 ACCEPTED SOLUTION
Community Support

Hi @Trist

Because not clear about your measure-MinSelectedDate structure, so I use slicer to get the selected date. Anyway, their logic is similar, so you can take it for reference.

-

Create the measure:

``````return interest value =
VAR _selDate =
MAX ( SlicerTable[SortCol] )
VAR _value =
CALCULATE (
SELECTEDVALUE ( 'Table'[interest value] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[ID] ),
'Table'[StartDate] <= _selDate
&& 'Table'[EndDate] > _selDate
)
)
RETURN
IF (
_value = 0,
CALCULATE (
MAX ( 'Table'[interest value] ),
ALLEXCEPT ( 'Table', 'Table'[ID] )
),
_value
)``````

Result:

See 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.

2 REPLIES 2
Community Support

Hi @Trist

Because not clear about your measure-MinSelectedDate structure, so I use slicer to get the selected date. Anyway, their logic is similar, so you can take it for reference.

-

Create the measure:

``````return interest value =
VAR _selDate =
MAX ( SlicerTable[SortCol] )
VAR _value =
CALCULATE (
SELECTEDVALUE ( 'Table'[interest value] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[ID] ),
'Table'[StartDate] <= _selDate
&& 'Table'[EndDate] > _selDate
)
)
RETURN
IF (
_value = 0,
CALCULATE (
MAX ( 'Table'[interest value] ),
ALLEXCEPT ( 'Table', 'Table'[ID] )
),
_value
)``````

Result:

See 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.

Super User

@Trist Sounds like a LOOKUPVALUE Range type of problem. https://community.powerbi.com/t5/Quick-Measures-Gallery/LOOKUPVALUE-Range/m-p/974201#M430

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Announcements

#### 2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.