Dynamic filtering problem with DAX

[vonschultz666]

Hi,

i have custom made DAX formula which dynamically generates a comma-separated list of StoreNo values from the raw unfiltered underlaying [SalesAnalysis] table based on the current dashboard selections of DivisionCode column and optionally StoreFormatCalculated column/slicer. I also use this logic as a KPI without CONCATENATEX, but with DISTINCTCOUNT.

Furthermore, i have matrix table chart with StoreNo columns, ItemNo rows and with SalesQuantity. My goal is to show matrix table only with those StoreNo values which are produced as a result of the mentioned formula. I want to show empty cells as well (StoreNo/ItemNo combinations with no data).

How to tackle that problem in the most efficient way? Calculated table? Visual-Level Filters? Something else?

This is the unfiltered view, i want to have P006 for the mentioned item to be shown empty when the Item is selected through a slicer. This isn't a case right now, therefore i need dynamic filtering

List = 
VAR SelectedStoreFormat = SELECTEDVALUE('[SalesAnalysis]'[StoreFormatCalculated], "ALL")
VAR CurrentFilteredDivisionCodes = VALUES('[SalesAnalysis]'[DivisionCode])
RETURN
IF(
    SelectedStoreFormat = "ALL", 
    CALCULATE(
        CONCATENATEX(VALUES('[SalesAnalysis]'[StoreNo]), '[SalesAnalysis]'[StoreNo], ", "),
        FILTER(
            ALL('[SalesAnalysis]'), // Removes any existing filters from the 'SalesAnalysis' table
            '[SalesAnalysis]'[DivisionCode] IN CurrentFilteredDivisionCodes
        )
    ),
    CALCULATE(
        CONCATENATEX(VALUES('[SalesAnalysis]'[StoreNo]), '[SalesAnalysis]'[StoreNo], ", "),
        FILTER(
            ALL('[SalesAnalysis]'), // Removes any existing filters, starting from a 'raw' state
            '[SalesAnalysis]'[DivisionCode] IN CurrentFilteredDivisionCodes &&
            '[SalesAnalysis]'[StoreFormatCalculated] = SelectedStoreFormat
        )
    )
)

[vonschultz666]

Unfortunately this isn't working. Calculated tables in Power BI don't directly respond to slicer changes or filter contexts the way measures do because they are calculated once when the data is loaded or refreshed, not dynamically with slicer interactions.

Original formula which uses CONCATENATEX generates correct StoreNo list, and that list i want to use in my column of the matrix table chart.

Calculated Table i've createad with the following code always gives me the same StoreNo list:

StoreNoListTable = 
VAR SelectedStoreFormat = SELECTEDVALUE('SalesAnalysis'[StoreFormatCalculated], "ALL")
VAR CurrentFilteredDivisionCodes = VALUES('SalesAnalysis'[DivisionCode])
RETURN
CALCULATETABLE(
    SELECTCOLUMNS(
        VALUES('SalesAnalysis'[StoreNo]),
        "StoreNo", 'SalesAnalysis'[StoreNo]
    ),
    FILTER(
        ALL('SalesAnalysis'),
        'SalesAnalysis'[DivisionCode] IN CurrentFilteredDivisionCodes &&
        (SelectedStoreFormat = "ALL" || 'SalesAnalysis'[StoreFormatCalculated] = SelectedStoreFormat)
    )
)

Can somebody help me please?

[some_bih]

Hi @vonschultz666 try Calculated table

[vonschultz666]

Hi, will calculated table change according to the supposed dynamic (logic) of my formula?

[some_bih]

If you apply your logic / measure and columns it should

[vonschultz666]

And than i should use columns from the calculated table to filter StoreNo on the main table?