Dax İteration İssue

[Trist]

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.

Thnaks in advance,

[v-xiaotang · Accepted solution]

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.

[Greg_Deckler]

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