Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
)
)
)
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?
Hi @vonschultz666 try Calculated table
Proud to be a Super User!
Hi, will calculated table change according to the supposed dynamic (logic) of my formula?
If you apply your logic / measure and columns it should
Proud to be a Super User!
And than i should use columns from the calculated table to filter StoreNo on the main table?
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
85 | |
83 | |
66 | |
60 | |
57 |
User | Count |
---|---|
188 | |
111 | |
105 | |
78 | |
71 |