CODEWITHSUNDEEP
powerbidax
Tayyab Vohra
Tayyab Vohra

Reputation: 1662

How to get the range of values from the slicer after the current selection in the slicer

I have a slicer in power bi , in which I am trying to get the all the slicer values which are after the current selection in the slicer, I am trying for a while and its not working , I have also tried to create the calculated table and its still not working for me , this is the measure which I am trying to create to get all the values after the current selection

RangeAfterSelection = 
VAR SelectedQuarter = SELECTEDVALUE(Query1[from_quarter])
RETURN
    CALCULATETABLE(
        DISTINCT(Query1[from_quarter]),
        FILTER(
            ALL(Query1),
            Query1[from_quarter] > SelectedQuarter &&
            RIGHT(Query1[from_quarter], 4) <= "2022"
        )
    )

So when I try to put this measure in the table , it gives me an error of

a table of multiple values was supplied where a single value was expected

I have values in the slicer text format like this from_quarter

2011-Q1
2011-Q2

Till 
2022-Q2

Data is like this

enter image description here

Upvotes: 0

Views: 251

Answers (1)

Amira Bedhiafi
Amira Bedhiafi

Reputation: 1

You can't only rely on the text values in you slicers, you need to add a numeric value to sort like below :

enter image description here

and try to order the from_quarter by the FromQYYYY :

enter image description here

And then create two new tables to use them as the slicers:

From = SELECTCOLUMNS(
    MyTable,
    "From", MyTable[from_quarter],
    "FromQYYYY", MyTable[FromQYYYY]
)

To = SELECTCOLUMNS(
    MyTable,
    "To", MyTable[from_quarter],
    "ToQYYYY", MyTable[FromQYYYY]
)

Don't create any relationships between the tables :

enter image description here

and then create the following measure :

CheckQuarter = 
VAR fromfilter = SELECTEDVALUE('From'[FromQYYYY])
VAR tofilter =  SELECTEDVALUE('To'[ToQYYYY])
RETURN 
IF(SELECTEDVALUE(MyTable[FromQYYYY]) >= fromfilter && SELECTEDVALUE(MyTable[ToQYYYY]) <= tofilter, 1)

Then to visualize the result, use the following :

  • slicer from : use the column From[From]
  • slicer to : use the column To[To]
  • in the visual displaying the result add the measure CheckQuarter=1 as a filter on the visual :

enter image description here

Upvotes: 1

Related Questions