Forum Discussion

t-dahen's avatar
t-dahen
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago
Solved

Remove All Filters but Specify New Filter

Hello,

 

Is there a way to remove all filters but specify a new filter inside a dax expression?

 

 

For example, what I would think to do is:

 

 

 

Measure = Calculate(SUM(Table[row1]), Table[row2] = "Something", ALLEXCEPT(Table, Table[row2]))

 

 

 

Need Help
  • edhans's avatar
    edhans
    6 years ago

    Try this. I used the logic I gave for the total, then used a slight different logic for the current total used as the numerator.

    Type A Total =
VAR varTypeATotal =
    SUMX(
        FILTER(
            ALL( 'Data Table' ),
            'Data Table'[Type] = "A"
        ),
        'Data Table'[Total]
    )
VAR varCurrentTotal =
    SUMX(
        FILTER(
            'Data Table',
            'Data Table'[Type] = "A"
        ),
        'Data Table'[Total]
    )
VAR varPercentOfTotal =
    DIVIDE(varCurrentTotal, varTypeATotal, 0)
RETURN
    varPercentOfTotal

     

     

     

     

7 Replies

  • edhans's avatar
    edhans
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Will this do what you need?

    Measure =
CALCULATE(
    SUM( Table[row1] ),
    FILTER(
        ALL( Table ),
        Table[Row2] = "Something"
    )
)

    It removes the filter from table, then returns only where the [Row2] field = "Something"