Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
)
)
)
Solved! Go to Solution.
Hi @olimilo ,
Please refer below steps, I create a sample for you.
1. below is my test table
Table:
Table2:
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
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.
@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])
Hi @olimilo ,
Please refer below steps, I create a sample for you.
1. below is my test table
Table:
Table2:
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
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.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.