Forum Discussion

VeselaAlena's avatar
VeselaAlena
Frequent Visitor
5 years ago
Solved

How to cancel filter context in virtual tables

I need to identify which products created 30% of total sales with virtual tables. I have succesfully identified the products in the virtual table but I'm not sure how to retrieve that information fro...
Need Help
  • VeselaAlena's avatar
    VeselaAlena
    5 years ago

    SOLVED:

    The first issue was filter context which I solved with CALCULATETABLE + REMOVEFILTERS. And then I needed to FILTER the final result after RETURN.

    cows =
VAR _totalHrubaMarze =
    CALCULATE (
        [Hrubá Marže Web Retail],
        REMOVEFILTERS ( 'product'[product name], 'product'[product id] )
    )
VAR _tabulka =
    CALCULATETABLE (
        ADDCOLUMNS (
            VALUES ( 'product'[product id] ),
            "Hruba Marze Produktu", [Hrubá Marže Web Retail]
        ),
        REMOVEFILTERS ( 'product'[product name] )
    )
VAR _tabulka_RunningTotal =
    CALCULATETABLE (
        ADDCOLUMNS (
            _tabulka,
            "RunningTotal",
                VAR CurrentProduct = [Hruba Marze Produktu]
                RETURN
                    SUMX (
                        FILTER ( _tabulka, [Hruba Marze Produktu] >= CurrentProduct ),
                        [Hruba Marze Produktu]
                    )
        ),
        REMOVEFILTERS ( 'product'[product name] )
    )
VAR _percentage =
    ADDCOLUMNS (
        _tabulka_RunningTotal,
        "Procenta", DIVIDE ( [RunningTotal], _totalHrubaMarze ),
        "Kravy", IF ( DIVIDE ( [RunningTotal], _totalHrubaMarze ) <= 0.5, 1, 0 )
    )
RETURN
    SUMX (
        FILTER (
            _percentage,
            SELECTEDVALUE ( 'product'[product id] ) = 'product'[product id]
        ),
        [Kravy]
    )

     

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
5 years ago

VeselaAlena Use ALL, ALLEXCEPT, KEEPFILTERS or REMOVEFILTERS to modify filter context. 

VeselaAlena's avatar
VeselaAlena
Frequent Visitor
5 years ago

Hi Greg_Deckler, thanks for pointing me to the right direction.

I used CALCULATETABLE + REMOVEFILTER as shown below. Now the matrix shows number 88 on all rows (88 is correct if I want to know how many "cows" products we have but I need to know specifically which products are the cows). So I think two options:

1.) There's a faulty filtering logic already in the table VAR_table_RunningTotal. Because when I want to display the values from [RunningTotal] column in matrix visualization it also gives me same number in all rows. 

2.) DAX is correct and I'm just missing the last piece of how to translate the result of my virtual tables into matrix.

 

Cows = 
VAR _TotalSales =
CALCULATE (
    [SalesWebl],
    REMOVEFILTERS ( 'product'[product name] )
)
VAR_table =
CALCULATETABLE (
    ADDCOLUMNS (
        VALUES ( 'product'[product id] ),
        "Product Sales", [SalesWebl]
    ),
    REMOVEFILTERS ( 'product'[product name] )
)
VAR_table_RunningTotal =
CALCULATETABLE (
    ADDCOLUMNS (
        _table,
        "RunningTotal",
            VAR CurrentProduct = [Product Sales]
            RETURN
                SUMX (
                    FILTER (
                        _table,
                        [Product Sales] >= CurrentProduct
                    ),
                    [Product Sales]
                )
    ),
    REMOVEFILTERS ( 'product'[product name] )
)
VAR_percentage =
CALCULATETABLE (
    ADDCOLUMNS (
        _table_RunningTotal,
        "Percent",
            DIVIDE (
                -- Transforming the running total into a percentage against the grand total of sales.
                [RunningTotal],
                _TotalSales
            ),
        "Cows",
            IF (
                DIVIDE (
                    [RunningTotal],
                    _TotalSales
                ) <= 0.3,
                1,
                0
            )
    ),
    REMOVEFILTERS ( 'product'[product name] )
)

VAR _onlyCowsTable = FILTER (_percentage, [Kravy] = 1 )


RETURN
-- option 1.):
SUMX (_table_RunningTotal,[RunningTotal])

-- option 2.):
--SUMX (_percentage,[cows])

 Desktop Matrix: