Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
olimilo
Responsive Resident
Responsive Resident

How to get the selected value of a slicer into a calculated column?

I have the following code that's supposed to check whether the selected value of a slicer is "Month-end" or "Normal". However, the SELECTEDVALUE() returns a blank in the formula. I would like to be able to get the selected value of the slicer into a calculated column and not a measure as I need to use it as a page-level filter - how can I do this?

 

 

IsPostedCM = 

VAR CurrentMonth = MONTH(NOW())
VAR CurrentYear = YEAR(NOW())

RETURN
    SWITCH(
        SELECTEDVALUE('MonthEndToggle'[MonthEnd]),
        FALSE, AND(YEAR([DatePosted]) = CurrentYear, MONTH([DatePosted]) = CurrentMonth),
        IF(CurrentMonth = 1,
            AND(
                YEAR([DatePosted]) = CurrentYear - 1,
                MONTH([DatePosted]) = 12
            ),
            AND(
                YEAR([DatePosted]) = CurrentYear,
                MONTH([DatePosted]) = CurrentMonth - 1
            )
        )
    )

 

 

1 ACCEPTED SOLUTION

Hi @olimilo ,

Please refer below steps, I create a sample for you.

1. below is my test table

Table:

vbinbinyumsft_0-1667371156497.png

Table2:

vbinbinyumsft_1-1667371174307.png

2. add a slicer visual with Table, add a table vissual with Table2

3. create a measure with below dax formula

Measure =
VAR cur_level =
    SELECTEDVALUE ( Table2[Level] )
VAR cur_sal =
    SELECTEDVALUE ( 'Table'[Sale] )
VAR tmp1 =
    CALCULATETABLE (
        VALUES ( 'Table'[Sale] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Sale] <= 20 )
    )
VAR tmp2 =
    CALCULATETABLE (
        VALUES ( 'Table'[Sale] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Sale] > 20 )
    )
VAR tmp3 =
    CALCULATETABLE (
        VALUES ( 'Table'[Sale] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Sale] > 50 )
    )
RETURN
    SWITCH (
        cur_level,
        "<=20", IF ( cur_sal IN tmp1, 1 ),
        ">20", IF ( cur_sal IN tmp2, 1 ),
        ">50", IF ( cur_sal IN tmp3, 1 ),
        1
    )

4. add the measure to the table visual filter in the filter pane

vbinbinyumsft_2-1667371349542.png

Animation9.gif

Please refer the attached .pbix file.

 

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@olimilo , You can not selectedvalue in a calculated column, you have to use a measure.

 

You can use measure in the visual level filter. As of now, you can not control the page-level filter with the selected value

Hi @amitchandak 

Bit confused how to do this; I created a measure like and included it in the table visual but the measure cannot be filtered as a visual-level filter (no values appear when expanding the measure value filter):

 

~Filter = SELECTEDVALUE('MonthEndToggle'[MonthEnd])

 

olimilo_0-1665974806363.png

 

Hi @olimilo ,

Please refer below steps, I create a sample for you.

1. below is my test table

Table:

vbinbinyumsft_0-1667371156497.png

Table2:

vbinbinyumsft_1-1667371174307.png

2. add a slicer visual with Table, add a table vissual with Table2

3. create a measure with below dax formula

Measure =
VAR cur_level =
    SELECTEDVALUE ( Table2[Level] )
VAR cur_sal =
    SELECTEDVALUE ( 'Table'[Sale] )
VAR tmp1 =
    CALCULATETABLE (
        VALUES ( 'Table'[Sale] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Sale] <= 20 )
    )
VAR tmp2 =
    CALCULATETABLE (
        VALUES ( 'Table'[Sale] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Sale] > 20 )
    )
VAR tmp3 =
    CALCULATETABLE (
        VALUES ( 'Table'[Sale] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Sale] > 50 )
    )
RETURN
    SWITCH (
        cur_level,
        "<=20", IF ( cur_sal IN tmp1, 1 ),
        ">20", IF ( cur_sal IN tmp2, 1 ),
        ">50", IF ( cur_sal IN tmp3, 1 ),
        1
    )

4. add the measure to the table visual filter in the filter pane

vbinbinyumsft_2-1667371349542.png

Animation9.gif

Please refer the attached .pbix file.

 

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

NO sure if this fix my need, but is the first simple explample I managed to get working. 

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.