Forum Discussion
VeselaAlena
5 years agoFrequent Visitor
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...
- 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] )
VeselaAlena
5 years agoFrequent Visitor
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]
)