Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

FInd ALL slicer selections within a string field

My primary fact table has a field (Products) that contains one or many products separated by semicolons. I have a second table with the unique product names, from which I've created a slicer. I would like to use the slicer to select records from the primary fact table where ALL of the selected slicer values appear in the Products field. In the example below, where Blue and Brown are selected in the slicer, I would like the primary table to filter to only Product 4341, the only row where BOTH Blue and Brown appear in Products. 

 

 

I am aware that I could use Data Transformation to break the rows in the primary data table, but don't want to. I am ultimately trying to provide users with a filtered table with records that meet the selected criteria, and would like to count those records.

  • olgad's avatar
    olgad
    1 year ago

    Ensure there’s no direct relationship between the Products column in the fact table and the slicer table. 

    Try this:

    FilteredRowCount =
    VAR SelectedProducts = VALUES(SlicerTable[ProductName]) -- Get selected slicer values
    VAR FactTableRows =
    ADDCOLUMNS(
    'FactTable',
    "@MatchCount",
    COUNTROWS(
    FILTER(
    SelectedProducts,
    SEARCH(";" & [ProductName] & ";", ";" & FactTable[Products] & ";", 1, 0) > 0
    )
    )
    )
    VAR FilteredRows =
    FILTER(
    FactTableRows,
    COUNTROWS(SelectedProducts) = [@MatchCount]
    )
    RETURN
    COUNTROWS(FilteredRows)

3 Replies

  • olgad's avatar
    olgad
    Icon for Resident Rockstar rankResident Rockstar
    1 year ago

    Ensure there’s no direct relationship between the Products column in the fact table and the slicer table. 

    Try this:

    FilteredRowCount =
    VAR SelectedProducts = VALUES(SlicerTable[ProductName]) -- Get selected slicer values
    VAR FactTableRows =
    ADDCOLUMNS(
    'FactTable',
    "@MatchCount",
    COUNTROWS(
    FILTER(
    SelectedProducts,
    SEARCH(";" & [ProductName] & ";", ";" & FactTable[Products] & ";", 1, 0) > 0
    )
    )
    )
    VAR FilteredRows =
    FILTER(
    FactTableRows,
    COUNTROWS(SelectedProducts) = [@MatchCount]
    )
    RETURN
    COUNTROWS(FilteredRows)

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    1 year ago

    Hi,

    Please check the below picture and the attached pbix file.

     

     

    PATH function (DAX) - DAX | Microsoft Learn

     

    PATHITEM function (DAX) - DAX | Microsoft Learn

     

    selected order id list = 
VAR _n =
    COUNTROWS ( ALL ( products ) )
VAR _filtercount =
    COUNTROWS ( products )
VAR _series =
    GENERATESERIES ( 1, _n, 1 )
VAR _path =
    ADDCOLUMNS ( 'order', "@path", SUBSTITUTE ( 'order'[products], "; ", "|" ) )
VAR _pathiteminrows =
    GENERATE (
        _series,
        VAR _number = [Value]
        RETURN
            ADDCOLUMNS ( _path, "@pathitem", PATHITEM ( [@path], _number ) )
    )
VAR _t =
    FILTER (
        SUMMARIZE ( _pathiteminrows, 'order'[order_id], [@pathitem] ),
        [@pathitem] <> BLANK ()
    )
VAR _productlist =
    VALUES ( products[products] )
VAR _selectedorderidlist =
    FILTER (
        ADDCOLUMNS (
            _t,
            "@filtercount",
                COUNTROWS (
                    FILTER (
                        _t,
                        EARLIER ( [order_id] ) = 'order'[order_id]
                            && [@pathitem] IN _productlist
                    )
                )
        ),
        [@filtercount] = _filtercount
    )
RETURN
    IF (
        ISFILTERED ( products[products] ),
        CONCATENATEX (
            SUMMARIZE ( _selectedorderidlist, 'order'[order_id] ),
            'order'[order_id],
            ", "
        )
    )

     

     

    count selected order id list = 
VAR _n =
    COUNTROWS ( ALL ( products ) )
VAR _filtercount =
    COUNTROWS ( products )
VAR _series =
    GENERATESERIES ( 1, _n, 1 )
VAR _path =
    ADDCOLUMNS ( 'order', "@path", SUBSTITUTE ( 'order'[products], "; ", "|" ) )
VAR _pathiteminrows =
    GENERATE (
        _series,
        VAR _number = [Value]
        RETURN
            ADDCOLUMNS ( _path, "@pathitem", PATHITEM ( [@path], _number ) )
    )
VAR _t =
    FILTER (
        SUMMARIZE ( _pathiteminrows, 'order'[order_id], [@pathitem] ),
        [@pathitem] <> BLANK ()
    )
VAR _productlist =
    VALUES ( products[products] )
VAR _selectedorderidlist =
    FILTER (
        ADDCOLUMNS (
            _t,
            "@filtercount",
                COUNTROWS (
                    FILTER (
                        _t,
                        EARLIER ( [order_id] ) = 'order'[order_id]
                            && [@pathitem] IN _productlist
                    )
                )
        ),
        [@filtercount] = _filtercount
    )
RETURN
    IF (
        ISFILTERED ( products[products] ),
        COUNTROWS ( SUMMARIZE ( _selectedorderidlist, 'order'[order_id] ) )
    )

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Thank you, Olgad. For others who might read this topic, I had to fuss a little with the arguments to SEARCH. Specifically, I eliminated the concatentations (all of the "&"s) so that my SEARCH looked more like the following:
     

    SEARCH (
           ( [SlicerSelection] ) ,
           ( [FactTableField] ),
           1, 0 ) > 0