Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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.

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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