Forum Discussion
Filter a Calculated Table With A Selected Slicer
Hi
I am trying to filter a calculated table with a slicer on the selected value in that slicer.
I have an InvoiceLines Table.
| Date | Product Category | Amount | InvoiceId |
| 20230101 | C01 | 100 | 123 |
| 20230102 | C20 | 200 | 456 |
I have a measure for Selected Category:
I then have a "Invoice By Year Month" Accumulation Calculated Table: Where I calculate a running total by year.
I am checking if the "Selected category" is Blank and returning a different Calculate function with the filter applied. No Selected Slicer
InvoiceByYearMonth =
FILTER(
SUMMARIZECOLUMNS(
vw_DW_Calendar[Year],
vw_DW_Calendar[Year MonthNum],
"Amount",SUM(vw_DW_InvoiceLines[Amount]),
"AccAmount",CALCULATE(
IF(ISBLANK([Selected Category]),
CALCULATE(
SUM(vw_DW_InvoiceLines[Amount]),
FILTER(
ALLSELECTED(vw_DW_Calendar[Year MonthNum]),
ISONORAFTER(vw_DW_Calendar[Year MonthNum],MAX(vw_DW_Calendar[Year MonthNum]),DESC)
)
),
CALCULATE(
SUM(vw_DW_InvoiceLines[Amount]),
FILTER(vw_DW_InvoiceLines,
vw_DW_InvoiceLines[CategoryName (groups)] = [Selected Category]
),
FILTER(
ALLSELECTED(vw_DW_Calendar[Year MonthNum]),
ISONORAFTER(vw_DW_Calendar[Year MonthNum],MAX(vw_DW_Calendar[Year MonthNum]),DESC)
)
)
)
)
),
vw_DW_Calendar[Year MonthNum] <= FORMAT(TODAY(),"yyyyMM") && vw_DW_Calendar[Year] >= Year(TODAY())-2
)
How would I filter the Product Category correctly as it is not switching between my calculate functions.
Thank you in advance
Snowflake , A calculated table can not take filter from slicer of the filter. You have to join it back to dimension table to pass the filter in visual, when you this table
1 Reply
- amitchandakSuper User
Snowflake , A calculated table can not take filter from slicer of the filter. You have to join it back to dimension table to pass the filter in visual, when you this table