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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
thisguy
Advocate I
Advocate I

SELECTEDVALUE alternate result not passing to filter - is there a way to do this?

I posted this previously but i don't think i was able to explain my problem correctly - I would like the signups vs target visual to show the latest month if nothing is selected in the signups by month visual - it currently shows the full history if nothing is selected.  

 

PBIX with sample data: 

https://drive.google.com/file/d/1khDD9vZDYgV14BDx-Ti1nJiStQGsrmGM/view?usp=sharing

 

if you select a month in the first visual the second visual filters correctly - if nothing is selected the second visual should show May (as per the selected month card) but it shows all months.  

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi,

I am not sure if I understood your question correctly, but please check the below measures and the attached pbix file.

 

SelectedMTD cumulative = 
VAR _maxdate =
    CALCULATE ( MAX ( Volumes[original_received_date] ), REMOVEFILTERS () )
VAR _maxmonthyear =
    MAXX (
        FILTER ( ALL ( DIM_Calendar ), DIM_Calendar[Date] = _maxdate ),
        DIM_Calendar[MonthYear]
    )
VAR _mtdmeasure =
    CALCULATE ( SUM ( Volumes[count per day] ), DATESMTD ( DIM_Calendar[Date] ) )
VAR SignUpsMTD =
    SWITCH (
        TRUE (),
        CALCULATE ( COUNTROWS ( VALUES ( DIM_Calendar[MonthYear] ) ), ALLSELECTED () ) = 1, _mtdmeasure,
        CALCULATE (
            SUM ( Volumes[count per day] ),
            DATESMTD ( DIM_Calendar[Date] ),
            DIM_Calendar[MonthYear] = _maxmonthyear
        )
    )
RETURN
    SignUpsMTD

 

Target MTD = 
VAR _maxdate =
    CALCULATE ( MAX ( Volumes[original_received_date] ), REMOVEFILTERS () )
VAR _maxmonthyear =
    MAXX (
        FILTER ( ALL ( DIM_Calendar ), DIM_Calendar[Date] = _maxdate ),
        DIM_Calendar[MonthYear]
    )
VAR _mtdmeasure =
    CALCULATE ( SUM ( Targets[Target] ), DATESMTD ( DIM_Calendar[Date] ) )
VAR SignUpsMTD =
    SWITCH (
        TRUE (),
        CALCULATE ( COUNTROWS ( VALUES ( DIM_Calendar[MonthYear] ) ), ALLSELECTED () ) = 1, _mtdmeasure,
        CALCULATE (
            SUM ( Targets[Target] ),
            DATESMTD ( DIM_Calendar[Date] ),
            DIM_Calendar[MonthYear] = _maxmonthyear
        )
    )
RETURN
    SignUpsMTD

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

3 REPLIES 3
tamerj1
Super User
Super User

Hi @thisguy 
Here is another way of doing that similar to yours

https://www.dropbox.com/t/OUOkIshFtRXkSqKf

Jihwan_Kim
Super User
Super User

Hi,

I am not sure if I understood your question correctly, but please check the below measures and the attached pbix file.

 

SelectedMTD cumulative = 
VAR _maxdate =
    CALCULATE ( MAX ( Volumes[original_received_date] ), REMOVEFILTERS () )
VAR _maxmonthyear =
    MAXX (
        FILTER ( ALL ( DIM_Calendar ), DIM_Calendar[Date] = _maxdate ),
        DIM_Calendar[MonthYear]
    )
VAR _mtdmeasure =
    CALCULATE ( SUM ( Volumes[count per day] ), DATESMTD ( DIM_Calendar[Date] ) )
VAR SignUpsMTD =
    SWITCH (
        TRUE (),
        CALCULATE ( COUNTROWS ( VALUES ( DIM_Calendar[MonthYear] ) ), ALLSELECTED () ) = 1, _mtdmeasure,
        CALCULATE (
            SUM ( Volumes[count per day] ),
            DATESMTD ( DIM_Calendar[Date] ),
            DIM_Calendar[MonthYear] = _maxmonthyear
        )
    )
RETURN
    SignUpsMTD

 

Target MTD = 
VAR _maxdate =
    CALCULATE ( MAX ( Volumes[original_received_date] ), REMOVEFILTERS () )
VAR _maxmonthyear =
    MAXX (
        FILTER ( ALL ( DIM_Calendar ), DIM_Calendar[Date] = _maxdate ),
        DIM_Calendar[MonthYear]
    )
VAR _mtdmeasure =
    CALCULATE ( SUM ( Targets[Target] ), DATESMTD ( DIM_Calendar[Date] ) )
VAR SignUpsMTD =
    SWITCH (
        TRUE (),
        CALCULATE ( COUNTROWS ( VALUES ( DIM_Calendar[MonthYear] ) ), ALLSELECTED () ) = 1, _mtdmeasure,
        CALCULATE (
            SUM ( Targets[Target] ),
            DATESMTD ( DIM_Calendar[Date] ),
            DIM_Calendar[MonthYear] = _maxmonthyear
        )
    )
RETURN
    SignUpsMTD

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


That works perfectly, thankyou so much 🙂

 

I thought i was close to working it out, but this is very different to my attempt  😕

Helpful resources

Announcements
Europe Fabric Conference

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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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