Forum Discussion

TomWoodward's avatar
TomWoodward
Frequent Visitor
3 years ago
Solved

Using Slicer Selection to Filter large table with multiple values in a column

I have a table of Purchase Orders. One column in this table is listing specifier information and may contain anything like the below, which is a list of architects and engineers assigned to each orde...
Need Help
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi TomWoodward ,

    I created a sample pbix file(see the attachment), please find the details in it.

    1. Create a measure as below

    Flag = 
VAR _userids =
    ALLSELECTED ( 'Users'[USERID] )
VAR _specifiers =
    CALCULATETABLE (
        VALUES ( 'Specifiers'[SPECIFIER] ),
        FILTER ( 'Specifiers', 'Specifiers'[USERID] IN _userids )
    )
VAR _specifier =
    MAX ( 'PO'[Specifier] )
VAR _spec1 =
    SUBSTITUTE ( _specifier, "ARCHITECT: ", "" )
VAR _spec2 =
    SUBSTITUTE ( _spec1, "ENGINEER: ", "" )
VAR mymeasure =
    SUBSTITUTE ( _spec2, ", ", "|" )
VAR Mylen =
    PATHLENGTH ( mymeasure )
VAR mytable =
    ADDCOLUMNS (
        GENERATESERIES ( 1, mylen ),
        "mylist", PATHITEM ( mymeasure, [Value] )
    )
VAR _specifiers2 =
    SELECTCOLUMNS ( mytable, "list", [mylist] )
VAR _count =
    COUNTROWS ( INTERSECT ( _specifiers, _specifiers2 ) )
RETURN
    IF ( _count > 0, 1, 0 )

    2. Create a visual and apply a visual-level filter with the condition(Flag is 1)

    Best Regards

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi TomWoodward ,

I created a sample pbix file(see the attachment), please find the details in it.

1. Create a measure as below

Flag = 
VAR _userids =
    ALLSELECTED ( 'Users'[USERID] )
VAR _specifiers =
    CALCULATETABLE (
        VALUES ( 'Specifiers'[SPECIFIER] ),
        FILTER ( 'Specifiers', 'Specifiers'[USERID] IN _userids )
    )
VAR _specifier =
    MAX ( 'PO'[Specifier] )
VAR _spec1 =
    SUBSTITUTE ( _specifier, "ARCHITECT: ", "" )
VAR _spec2 =
    SUBSTITUTE ( _spec1, "ENGINEER: ", "" )
VAR mymeasure =
    SUBSTITUTE ( _spec2, ", ", "|" )
VAR Mylen =
    PATHLENGTH ( mymeasure )
VAR mytable =
    ADDCOLUMNS (
        GENERATESERIES ( 1, mylen ),
        "mylist", PATHITEM ( mymeasure, [Value] )
    )
VAR _specifiers2 =
    SELECTCOLUMNS ( mytable, "list", [mylist] )
VAR _count =
    COUNTROWS ( INTERSECT ( _specifiers, _specifiers2 ) )
RETURN
    IF ( _count > 0, 1, 0 )

2. Create a visual and apply a visual-level filter with the condition(Flag is 1)

Best Regards

  • TomWoodward's avatar
    TomWoodward
    Frequent Visitor
    2 years ago

    This did work for me! thank you very much. But I do have a follow up question.

     

    Now that I am able to have a visual for the PO's and a slicer for the user, I also need to be able to filter this table by year. There is a "date" column within the PO table that I have related to a "dates" table. Normally, I have been applying a filter to each visual to limit it to a certain year. This way, I can have a 2021, a 2022, and a 2023 visual that are all filtered by the user selected and the specifiers they call on.

     

    When I set up these visuals as you have shown, then add a date filter to the visual, I get an error saying "MdxScript(Model) (115, 9) Calculation error in measure 'PO Listing 2009-Present'[Flag]: The arguments in GenerateSeries function cannot be blank", is there a way to fix this? or other way for me to create filtered by year visuals?