CODEWITHSUNDEEP
powerbidax
t0rres
t0rres

Reputation: 211

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

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.

enter image description here

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.

enter image description here

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.

enter image description here

Upvotes: 0

Views: 1631

Answers (1)

Olly
Olly

Reputation: 7891

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 )
        )
    )

Upvotes: 1

Related Questions