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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.