Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

SWITCH and ISFILTERED combination

Hi all

I am using a measure as follows:

 

Fixed cost var filters = 
SWITCH(
    TRUE(),
    ISFILTERED(dDate[Year]),
    DIVIDE(
        CALCULATE(
            SUM('Fixed Cost per Month'[fixed Cost]),
            ALL('Fixed Cost per Month'[fixed Cost])
        )
        *MAX(dDate[Month Number]),
        [Dedicated Shipments]
    ),
      ISFILTERED(dDate[Month]),
      DIVIDE(
         SUM('Fixed Cost per Month'[fixed Cost]),
            [Dedicated Shipments]
        )              
)

 


This when fed to visualization gives the below:

for only [Year] filtered

For [Months] filtered

What I actually want from the measure is to give the "Actual Required" column values which is [Sum fixed cost / Dedicated Shipment]

MonthDedicated ShipmentsSum fixed costFixed cost var filtersActual required
Jan1800910000505.56505.56
Feb3288910000553.53276.76
Mar3553910000768.36256.12
Apr3726910000976.92244.23
May43979100001034.8206.96
Jun39659100001377.05229.51
Jul33819100001884.06269.15
Aug55029100001323.16165.39
Sep63969100001280.49142.28
Oct66029100001378.37137.84
Nov76569100001307.47118.86
Dec59289100001842.11153.51

 

Here,

 

Dedicated Shipments = CALCULATE([Shipments],'Prototype Modeling_Freight'[Transporter Type]="DEDICATED")

 

 

Please let me know if any additional info is required here for you to arrive to a conclusion.

All your comments and suggestions are greatly appreciated

Dax commands help
Help Requested
isfiltered
switch
  • Anonymous's avatar
    Anonymous
    6 years ago

    Using INSCOPE instead of ISFILTER does the job

    Fixed cost var filters = 
SWITCH(
    TRUE(),
    ISINSCOPE(dDate[Year]),
    DIVIDE(
        CALCULATE(
            SUM('Fixed Cost per Month'[fixed Cost]),
            ALL('Fixed Cost per Month'[fixed Cost])
        )
        *MAX(dDate[Month Number]),
        [Dedicated Shipments]
    ),
        ISINSCOPE(dDate[Month]),
      DIVIDE(
         SUM('Fixed Cost per Month'[fixed Cost]),
            [Dedicated Shipments]
        ),
        ISINSCOPE('Prototype Modeling_Freight'[Plant Cluster]),
        DIVIDE(
            SUM('Fixed Cost per Month'[fixed Cost]),
            [Dedicated Shipments]  

                ),
                ISINSCOPE('Equipment Master'[Max Loadability in Tonns]),
                
                DIVIDE(VALUES('Fixed Cost per Month'[fixed Cost]),[Dedicated Shipments]),BLANK()
    )

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Using INSCOPE instead of ISFILTER does the job

    Fixed cost var filters = 
SWITCH(
    TRUE(),
    ISINSCOPE(dDate[Year]),
    DIVIDE(
        CALCULATE(
            SUM('Fixed Cost per Month'[fixed Cost]),
            ALL('Fixed Cost per Month'[fixed Cost])
        )
        *MAX(dDate[Month Number]),
        [Dedicated Shipments]
    ),
        ISINSCOPE(dDate[Month]),
      DIVIDE(
         SUM('Fixed Cost per Month'[fixed Cost]),
            [Dedicated Shipments]
        ),
        ISINSCOPE('Prototype Modeling_Freight'[Plant Cluster]),
        DIVIDE(
            SUM('Fixed Cost per Month'[fixed Cost]),
            [Dedicated Shipments]  

                ),
                ISINSCOPE('Equipment Master'[Max Loadability in Tonns]),
                
                DIVIDE(VALUES('Fixed Cost per Month'[fixed Cost]),[Dedicated Shipments]),BLANK()
    )