Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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,
Solved! Go to 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.
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.
@Trist Sounds like a LOOKUPVALUE Range type of problem. https://community.powerbi.com/t5/Quick-Measures-Gallery/LOOKUPVALUE-Range/m-p/974201#M430
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |