Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Error on Switch measure formula

Hi All, I'm trying to visualise a line graph with count of sales over time (month) between two countries A& B and the Total Sales count(Country A+B). So the Idea is to have a slicer with Country A...
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    I suggest having a disconnected slicer table like below.

    Please check the below picture and the attached pbix file.

     

     

    QTY total: =
    VAR selectedcountry =
    VALUES ( 'Country Slicer'[Country] )
    VAR unselectedcountry =
    EXCEPT ( VALUES ( Country[Country] ), selectedcountry )
    RETURN
    SWITCH (
    SELECTEDVALUE ( 'Country Slicer'[ID] ),
    3,
    SWITCH (
    SELECTEDVALUE ( Country[ID] ),
    3, CALCULATE ( SUM ( Sales[Qty] ), ALL ( country ) ),
    SUM ( Sales[Qty] )
    ),
    SWITCH (
    SELECTEDVALUE ( Country[ID] ),
    3, CALCULATE ( SUM ( Sales[Qty] ), ALL ( country ) ),
    CALCULATE (
    SUM ( Sales[Qty] ),
    KEEPFILTERS ( NOT ( Country[Country] IN unselectedcountry ) )
    )
    )
    )
Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
4 years ago

Hi,

I suggest having a disconnected slicer table like below.

Please check the below picture and the attached pbix file.

 

 

QTY total: =
VAR selectedcountry =
VALUES ( 'Country Slicer'[Country] )
VAR unselectedcountry =
EXCEPT ( VALUES ( Country[Country] ), selectedcountry )
RETURN
SWITCH (
SELECTEDVALUE ( 'Country Slicer'[ID] ),
3,
SWITCH (
SELECTEDVALUE ( Country[ID] ),
3, CALCULATE ( SUM ( Sales[Qty] ), ALL ( country ) ),
SUM ( Sales[Qty] )
),
SWITCH (
SELECTEDVALUE ( Country[ID] ),
3, CALCULATE ( SUM ( Sales[Qty] ), ALL ( country ) ),
CALCULATE (
SUM ( Sales[Qty] ),
KEEPFILTERS ( NOT ( Country[Country] IN unselectedcountry ) )
)
)
)