DAX LookupValue based on SelectedValue and LookupValue in a measure

[abacc22]

Hi there,

I want to be able to show a value from table `Points_MaxValue` based on a Selected Date Value (this part is working, looking at the columns `ValidFrom` and `ValidTo` date) and another Value in a different column `Points` in that certain table.

In the measure below it would work if it is just looking for a value based on the Selected Date. Unfortunately, I also want it to lookup another value. Currently the VAR _Pts is totally ignored in the Calculate column.

Points_MaxValue = 

VAR _date =
    SELECTEDVALUE ( Calendar_NoRelation[Date] )
VAR _Pts = DISTINCT(Objects_Points[ObjectPoints_Total])
VAR _vA =
    CALCULATE (
        MAX ( 'Points_MaxValue'[MaxValue] ) ,
        FILTER (
            ( 'Points_MaxValue' ),
            _date >= 'Points_MaxValue'[VALIDFROM]
                && _date <= 'Points_MaxValue'[VALIDTO]
                && _Pts = Points_MaxValue[Points]
        )
    )
RETURN
    IF ( _vA <> BLANK (), _vA, "NULL" )

Link to the .pbix file:

https://1drv.ms/u/s!AliMmiR5bX0C3RYlZF13eUmvUyAc?e=afjLbN

Tables in this file:

Objects 1:1 Objects_Points

Points_MaxValue(Points) 1:n Objects_Points(ObjectPoints_Total)

Points_MaxValue have per amount of points a certain 'MaxValue' that I want to show per record (ObjectID) in a table. Since every Object has a certain amount of points, it should lookup the right value that belongs the a certain amount of Points. However, the date filter selected selects the Date range of which the Lookup should be based on. Currently the data set has only records ValidFrom 1-7-2020 to 30-6-2021. It should show NULL before and after those dates. Later I will fill other years' data in this table that it should lookup.

Kind regards,

Erik

[v-yingjl]

Hi @abacc22  @Anonymous ,

Glad to hear the issue is solved. You can accept the suitable reply as solution, that way, other community members could easily find the answer when they get same issues.

Best Regards,

Yingjie Li

[dedelman_clng]

Hi @abacc22 

I downloaded your PBIX but it appears to be working as expected. When I chose date of 2020-6-9, I got NULL for Points_MaxValue.

Can you provide screenshots of what you are seeing and be very specific about what you are expecting to see?

David

[Anonymous]

Responding via other account, since I'm currently working from home where I don't have the login details!

First of all, I think the date selection is a bit confusing, since it's text and the format is YYYY-MM-DD. Since I have table 'Points_MaxValue' with only data in between 2020-7-1 until 2021-6-30, the 2020-6-9 filter didn't show any value. Currently I updated the table with data from 2017-6-30 until 2020-6-30 so you now will be able to see a difference. Mark the difference when you now select after 2020-7-1: the numbers on each record increase by ~100 per record.

What I want it to do is let the measure show the right value per records, but currently it aggregates somehow instead of showing the correct value per record, since I have no clue how to put in the 'Objectpoints_Total' column as input for the measure, while using selectedvalue of 'Peildatum' simultaneously.

So in the example below I select 2021-6-9, and it clearly reacts to that by showing values related to that period, since if I would select a value before 2020-7-1 it would lower the output. However, it does not show the right value, since it doesn't use my 'ObjectPoints_Total' as the second variable, since I don't know where to put it in the measure.

For instance, I expect 393,14 instead of 493 for Object 11160, 11161, 11185 and 11189, since they have 97 points. 97 points as shown in the table to the right corresponds to the value of 393,14.

Maybe this makes more sense?

Kind regards,

Erik

[dedelman_clng]

"I have no clue how to put in the 'Objectpoints_Total' column as input for the measure, while using selectedvalue of 'Peildatum' simultaneously."

When you put a measure into a table the way you are, every other non-measure value in that row becomes its own implicit slicer/filter.  So SELECTEDVALUE(ObjectPoints_Total) should work the same as if that value were in a slicer.  You can do as many SELECTEDVALUE or LOOKUPVALUE into variables within your code as you need.  Try that and see if you have any more success (I'm not in a position to be able to download a new copy of your pbix).

David

[Anonymous · Accepted solution]

I didn't know that! I solved it by simply putting SELECTEDVALUE around it in the variable. Thanks!

Points_MaxValue = 

VAR _date =
    SELECTEDVALUE ( Calendar_NoRelation[Date] )
VAR _Pts = 
    SELECTEDVALUE( Objects_Points[ObjectPoints_Total] )
VAR _vA =
    CALCULATE (
        MAX ( 'Points_MaxValue'[MaxValue] ) ,
        FILTER (
            ( 'Points_MaxValue' ),
            _date >= 'Points_MaxValue'[VALIDFROM]
                && _date <= 'Points_MaxValue'[VALIDTO]
                && _Pts = Points_MaxValue[Points]
        )
    )
RETURN
    IF ( _vA <> BLANK (), _vA, "NULL" )