Switching Measure in a line chart with the Slicer selection on Power BI

Question

I have a line chart that shows YTD revenue for the last 3 years and a line for Goal.

I have 3 slicers Region, District, and Branch all coming from the table DIMBRANCH

I have the following goals tables:

goalbyRegion

goalbyBranch

goalbyCompany

Requirement When nothing on the slicer is selected then the goal from table goalbycompany should be displayed. when a region is selected, show goal on the line chart of the selected region coming in from goalbyregion table.

Problem Currently the revenue changes over slicer selection because the revenue is coming from a single table tbl_revenue.

The image shows the revenue and goal line charts if nothing is selected on the slicer.

DAX currently used:

2017/Revenue = 
VAR _year =
    YEAR ( TODAY () ) - 2
RETURN
    CALCULATE (
        SUM ( Revenue[Revenue] ),
        FILTER ( Revenue,Revenue[Year] = _year )
    )


2018/Revenue = 
VAR _year =
    YEAR ( TODAY () ) - 1
RETURN
    CALCULATE (
        SUM ( Revenue[Revenue] ),
        FILTER ( Revenue,Revenue[Year] = _year )
    )



2019/Revenue = 
VAR _year =
    YEAR ( TODAY () ) 
RETURN
    CALCULATE (
        SUM ( Revenue[Revenue] ),
        FILTER ( Revenue,Revenue[Year] = _year )
    )


2019/Goals Amount = 
VAR _year =
    YEAR ( TODAY () ) 
RETURN
    CALCULATE (
        SUM ( GoalByCompany[GoalAmount] ),
        FILTER ( GoalByCompany, GoalByCompany[Year] = _year )
    )

DAX I am trying to use to achive my goal with switch function.

Switch Goal = 
var _region = SELECTEDVALUE(Branch[Region])
var _branch = SELECTEDVALUE(Branch[Branch])
var _district = SELECTEDVALUE(Branch[District])

var _year = year(TODAY())
var _goalregion = CALCULATE(
                    SUM(GoalByRegion[GoalAmount]),
                    FILTER(GoalByRegion , GoalByRegion[year] = _year))

var _regionselection = 
    SWITCH(_region,
        "RegionGoal",_goalregion
    )

return 
switch (_regionselection,"Region",_goalregion,blank())

I am not used to the switch function yet. I thought I could use this to get to my requirement.

this is when I select a region.

Here is the relationships between my tables

Additonal tables: revenue table links to branch table with branchID calendarweek table links to date table via weekendingdate column.

Please edit your question to show your current DAX measure, and relationships between tables — Olly
@Olly Updated my question with more details as you asked Please let me know if you need any other info. — Success Maharjan
An alternative approach would be if you add three charts with different axis and save three different views as bookmark. With a button you could switch between the three different bookmarked views. — Strawberryshrub

Olly Olly · Accepted Answer · 2019-09-11T10:39:20

You don't need to use SWITCH, if you're only considering whether Region is filtered or not. You can simply use an IF statement, to decide which measure to calculate:

2019/Goals Amount = 
VAR _year = 
    YEAR ( TODAY() )

RETURN
    IF ( 
        HASONEFILTER ( Branch[Region] ),
        CALCULATE (
            SUM ( GoalByRegion[GoalAmount] ),
            FILTER ( GoalByRegion, GoalByRegion[Year] = _year )
        ),
        CALCULATE (
            SUM ( GoalByCompany[GoalAmount] ),
            FILTER ( GoalByCompany, GoalByCompany[Year] = _year )
        )
    )

Switching Measure in a line chart with the Slicer selection on Power BI

1 Answers