Dynamic vs. fixed columns in matrix table - filtering problem

[vonschultz666]

Hi everyone,

I have matrix table with SalesQuantity values, ItemNo for rows and StoreNo for columns. Ceratin fields are empty when there are no sales (i've also tried adding zeros to the empty fields but the matrix table becomes really slow with that solution - https://youtu.be/mOgvAR-t90Y). I also have star schema with StoreDetails and ItemDetails table with distinct list of Stores & Items, but i'm not using that in this visual, only main table.

There is also a column called StoreFormatCalculated and DivisionCode. StoreFormatCalculated can be different for certain combinations of StoreNo and DivisionCode, for example for StoreNo=P002, StoreFormatCalculated can be different in the case of DivisionCode=100 or DivisionCode=200.

When filtered by Item, I want to show all StoreNo for the specific DivisionCode of the Item, no matter if SalesQuantity is empty or not.

Currently, this is the unfiltered view:

...and when i filter it by ItemNo, empty fields are gone missing:

...furthermore, when i filter it by StoreFormatCalculated, i also want all StoreNo for the DivisionCode of the ItemNo to be shown, which isn't the case now, because only non-empty values are shown:

How to achieve that?

[Anonymous]

Hi @vonschultz666,

1. Instead of relying on the raw values, consider creating a DAX measure that handles empty values explicitly.

SalesQuantityDisplay = IF(ISBLANK([YourMeasureName]), 0, [YourMeasureName]))
 
2. If this doesn't work, consider creating a unconnected table in powerquery:

And then please create a measure:

Measure = 
VAR __cur_item = SELECTEDVALUE(Table2[Item No])
VAR __cur_store = SELECTEDVALUE('Table2'[StoreNo])
VAR __result = CALCULATE([YourMeasureName],'Table'[StoreNo]=__cur_store && 'Table'[Item No]=__cur_item)
RETURN
__result

Remenber check the show items with no data option of row and column:

OutPut:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[vonschultz666]

Hi @Anonymous 

i think i've constructed my question wrongly. I've manage to arrange the DAX formula which lists all the StoreNo's for every combination of the DivisionCode and StoreNo of the selected ItemNo's DivisionCode. I want to have that list of StoreNo's in top column of my matrix table with ItemNo, StoreNo and SalesQuantity values for the selected ItemNo, EVEN if that means listing mentioned combinations without SalesQuantity (ie. empty cells where SalesQuantity for the given StoreNo & ItemNo combination doesn't exist). How can i achieve that? This is some sort of dynamic filtering which applies when ItemNo slicer is activated.

This is the formula which works more or less, i just need to "push" this list into my main matrix table:

▲ IsRelevantStoreFormat = 
VAR SelectedItemNo = SELECTEDVALUE('[SalesAnalysis]'[ItemNo])
VAR SelectedDivisionCode = LOOKUPVALUE('[SalesAnalysis]'[DivisionCode], '[SalesAnalysis]'[ItemNo], SelectedItemNo, BLANK())
VAR SelectedStoreFormat = SELECTEDVALUE('[SalesAnalysis]'[StoreFormatCalculated])
RETURN
IF(
    ISBLANK(SelectedStoreFormat),
    CONCATENATEX(VALUES('[SalesAnalysis]'[StoreNo]), '[SalesAnalysis]'[StoreNo], ", "),
    CALCULATE(
        CONCATENATEX(VALUES('[SalesAnalysis]'[StoreNo]), '[SalesAnalysis]'[StoreNo], ", "),
        FILTER(
            ALL('[SalesAnalysis]'),
            '[SalesAnalysis]'[DivisionCode] = SelectedDivisionCode &&
            '[SalesAnalysis]'[StoreFormatCalculated] = SelectedStoreFormat
        )
    )
)

[vonschultz666]

Help would be much appreciated!