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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
thisguy
Advocate I
Advocate I

SELECTEDVALUE alternate result is calculating correctly but not working with filter

I have what i thought was a fairly simple measure to allow a visual to be filtered by selection and default to the current month if nothing is selected: 

 

the measure works as expected if a month is selected, it even returns the correct value if nothing is selected (checked by putting it in a card) but the visual is not filtering to the defult (current) month if nothing is selected.  i made the default month a variable to see if that would fix and it is still not working -  I'm stuck as to what to try next?

 

The Measure: 

Selected Month =
VAR defaultmonth =
CALCULATE (
DISTINCT ( DIM_Calendar[MonthYear] ),
DIM_Calendar[CurMonthOffset] = 0
)
RETURN
SELECTEDVALUE ( DIM_Calendar[MonthYear], defaultmonth )
 
 
 
 
4 REPLIES 4
vapid128
Solution Specialist
Solution Specialist

IF(

DISTINCTCOUNT( DIM_Calendar[MonthYear] )=1,

the correct value,

the defult (current)value

)

i tried this and it didnt work, the card now shows blank if nothing is selected: 

 

Selected Month =
IF (
    DISTINCTCOUNT ( DIM_Calendar[MonthYear] ) = 1,
    SELECTEDVALUE (
        DIM_Calendar[MonthYear],
        CALCULATE (
            DISTINCT ( DIM_Calendar[MonthYear] ),
            DIM_Calendar[CurMonthOffset] = 0
        )
    )
)

vapid128
Solution Specialist
Solution Specialist

IS this what you want?

屏幕截图 2022-06-07 174605.png屏幕截图 2022-06-07 174605.png

 

my measure does that the problem is when the alternate result is returned for selected value it doesnt pass to a filter.   note it is returning the correct value just not filtering.   

 

i tried to simplify the dax to make it easier to trouble shoot - the full measure is here: 

 

SelectedMTD cumulative =
VAR selectedmonth =
SELECTEDVALUE ( DIM_Calendar[MonthYear], CALCULATE (
DISTINCT ( DIM_Calendar[MonthYear] ),
DIM_Calendar[CurMonthOffset] = 0
) )
var SignUpsMTD =
CALCULATE (
DISTINCTCOUNT ( training_contracts[SUT ID ] ),
DATESMTD ( DIM_Calendar[Date] ),
DIM_Calendar[MonthYear] = selectedmonth
)
RETURN
IF (
MAX ( DIM_Calendar[Date] ) <= LASTDATE ( training_contracts[tyims_created_on] ),
signupsmtd,
BLANK ()
)

 

If i select a month it filters correctly: 

thisguy_0-1654822821097.png

if no date is selected it shows all the data: 

thisguy_1-1654822880726.png

 

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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