Forum Discussion

MarcoW91's avatar
MarcoW91
Frequent Visitor
3 years ago

Trying to create a DAX code in which I can only apply the formula on a filtered selection

Hi all,

 

I got the following dax code:

 

Future Forecast =
 
    SUMX(
    'FC UnderRev',
    'FC UnderRev'[COS URev_FC] +
    'FC UnderRev'[COS URev_FC] * [WirePercentage] +
    'FC UnderRev'[COS URev_FC] * [WireRodPercentage] +
    'FC UnderRev'[COS URev_FC] * [ProfilePercentage] +
    'FC UnderRev'[COS URev_FC] * [PowderPercentage]
)
 
I want to apply this code only on the selected items from a filter which filters on DIM_SPGHierarchy[SPG].
If the item is not selected in the filter I want to apply the following formula:
  SUMX(
    'FC UnderRev',
    'FC UnderRev'[COS URev_FC])
 
This column DIM_SPGHierarchy[SPG] is also present in the endresult table and therefor is causing an issue as it will filter out the items which I put in the filter. I cannot get it to work with disconnecting filters to apply both formula's in the two cases (filtered out and filtered in).
 
Can someone please assist?
 
Need Help

2 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    3 years ago
    I want to apply this code only on the selected items from a filter

    There's a DAX function for that - FILTERS().  Also read about VALUES() and ISFILTERED().

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi MarcoW91,

    You can add a variable to store the selection items, then you can add if statements in your expression to achieve parameterized calculations:

    Future Forecast =
VAR selection =
    VALUES ( DIM_SPGHierarchy[SPG] )
RETURN
    SUMX (
        'FC UnderRev',
        'FC UnderRev'[COS URev_FC]
            * (
                1
                    + IF ( "WirePercentage" IN selection, [WirePercentage] )
                    + IF ( "WireRodPercentage" IN selection, [WireRodPercentage] )
                    + IF ( "ProfilePercentage" IN selection, [ProfilePercentage] )
                    + IF ( "PowderPercentage" IN selection, [PowderPercentage] )
            )
    )

    Regards,

    Xiaoxin Sheng