Forum Discussion

Snowflake's avatar
Snowflake
Frequent Visitor
3 years ago
Solved

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. 

DateProduct CategoryAmountInvoiceId
20230101C01100123
20230102C20200456


I have a measure for Selected Category:

Selected Category = SELECTEDVALUE(vw_DW_InvoiceLines[CategoryName (groups)]).


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

  • 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