Home Forums Power BI Creating a measure that extract a data range around a filter selection

This topic contains 0 replies, has 1 voice, and was last updated by  izakdekock 1 week, 1 day ago.

Viewing 1 post (of 1 total)
  • Author
    Posts
  • #11139

    izakdekock
    Participant
    • Started: 1
    • Replies: 0
    • Total: 1

    Good day

    I have a data set that contains an identifier column and a value column.

    A simplified version of my data set:
    Identifier;   Value
    1,                     30
    2,                    31
    3,                     32
    4,                     33
    5,                     34
    6,                     35
    7,                     36
    8,                     37
    9,                     38
    10,                   39

    I need to create a graph that, when i filter to one specific identifier, will not show only the Value associated with the selected identifier, but also a range of data adjacent to it.

    When I use a slicer and select the identifier 5, for example, I need the measure to contain the values 33,34,35,36 and 37 with the rest of the values blanked out.

    I manage to do this with the following DAX code, using static limits:

    FilteredValue = IF(
    VALUES(Table1[Index]) >= 3 &&
    VALUES(Table1[Index]) <= 7,
    MAX(Table1[Value]), BLANK())

    This  shows only the values corresponding to the identifiers between and including 3 and 7.

    However, I would like to make the limits dynamic, based on the current identifier selected in a slicer.   I therefore adjusted the code to the following:

    FilteredValue = IF(
    VALUES(Table1[Index]) >= (SELECTEDVALUE(Table1[Index]) -2) &&
    VALUES(Table1[Index]) <= (SELECTEDVALUE(Table1[Index]) +2),
    MAX(Table1[Value]), BLANK())

    This does not work.  The measure still contains the complete range and I can’t understand why, since SELECTEDVALUE(Table1[Index]) does return the value of 5, when 5 is selected.

    I also tried the following:

    FilteredValue = CALCULATE(
    VALUES(Table1[Value]),
    FILTER(Table1,
    Table1[Index]>=SELECTEDVALUE(Table1[Index]) – 2 && Table1[Index]>=SELECTEDVALUE(Table1[Index]) +2))

    This measure does not contain any data.

    Any help would be highly appreciated.

    Regards!

Viewing 1 post (of 1 total)

You must be logged in to reply to this topic.