cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Trist
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
IDinterest value StartDateEndDateMinSelectedDate(measure)
10000000000071015.2.202116.5.202131.3.2021
1000000000007816.5.202117.6.2021 
1000000000007617.6.202115.8.2021 
10000000000071515.8.202112.11.2023 
100000000004708.3.20217.5.2021 
10000000000470.247.5.202115.8.2021 
10000000000470.8915.8.202114.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,

1 ACCEPTED SOLUTION
v-xiaotang
Community Support
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:

v-xiaotang_0-1621599692601.png

v-xiaotang_1-1621599692603.png

 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.

View solution in original post

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

v-xiaotang_0-1621599692601.png

v-xiaotang_1-1621599692603.png

 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.

View solution in original post

Greg_Deckler
Super User
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!

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

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.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors