topic Re: To get the Selected and previous to the selected Value in DAX Commands and Tips

To get the Selected and previous to the selected Value

Anonymous — Thu, 21 May 2020 11:23:20 GMT

Hi Team,

I am new to PowerBI and getting familier with the tool and DAX.

I have the following Data:

Qtr	Index
2019Q1	1
2019Q2	2
2019Q3	3
2019Q4	4

I have a slicer on Qtr.

Need to get the selected Slicer Value and its previous value.

Example:

Selected slicer value is "2019Q3"

Result on Card should be : "2019Q3 & 2019Q2"

Result on card if no slicer Selection: "2019Q4 & 2019Q3" (Max value and its previous value)

Thanks in advance

Re: To get the Selected and previous to the selected Value

Pragati11 — Thu, 21 May 2020 16:53:54 GMT

Hi Anonymous ,

You can follow the steps below to achieve the desired result:

The sample data used is

2. Create a column as follows:

3. Create the following calculated column:

4. Create the following measure:

5. Move the above created measure "DISPLAYVALUECARD" to a card visual and create a slicer with QUARTER column:

This shows the dafault value which you asked for when nothing is selected in the filter.

6. Let's select a value:

Desired result is displayed.

Similarly, if you check other filter selections, the desired value is displayed in the card.

Hope this solution helps.

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati

Re: To get the Selected and previous to the selected Value

Pragati11 — Fri, 22 May 2020 08:00:24 GMT

Hi Anonymous ,

If my solution helped you, can you please mark and accept it as a solution for others to refer in future.

Thanks,

Pragati

Re: To get the Selected and previous to the selected Value

ash123 — Fri, 22 May 2020 08:28:45 GMT

Hi ,

There is also another way of achieving the functionality you are looking for to be inplemented in your report.

Step 1 : Create a measure to capture previous Qtr based on your selection .

Previous_Qtr =
IF (
ISFILTERED ( 'Table'[Qtr] ),
LOOKUPVALUE ( 'Table'[Qtr], 'Table'[Index], MAX ( 'Table'[Index] ) - 1 )
)

Step 2 : Create another measure select_measure which you have to place in the card. Here when you have selected any Qtr then that qtr and previous qtr will be shown else last qtr and its previous qtr will be shown.

select_measure =
IF (
    ISFILTERED ( 'Table'[Qtr] ),
    MAX ( 'Table'[Qtr] ) & " & " & [Previous_Qtr],
    LOOKUPVALUE ( 'Table'[Qtr], 'Table'[Index], MAX ( 'Table'[Index] ) ) & " & "
        & LOOKUPVALUE ( 'Table'[Qtr], 'Table'[Index], MAX ( 'Table'[Index] ) - 1 )
)

Also, It is suggested not to use calculated column as it hampers performance.

Hope it helps.

Thanks.

Kaushlendra Mishra

www.powerbitalks.com

Linkedin: https://www.linkedin.com/in/kaushlendra-mishra-49627310/