Forum Discussion

gluizqueiroz's avatar
gluizqueiroz
Icon for Resolver I rankResolver I
7 years ago
Solved

How to create a slice filter with a measure.

I have a measure that returns me 2 words:
"Sold" or "Unsold"

I wanna create a slicer like the following and when user click on button, the slicer filter a visual table showing only the products selected (The products sold or the unsold or both, if user select both options, like a normal slicer)


But, like as you know, I can't put a mesure on a slicer, it needs to be a real column. 
How can I create this slicer?
I search on internet and find something about create a exclusive table with 1 column and 2 lines (Sold and Unsold), put this column on a slicer and create a measure to link them, but I don't know if this solution works for my problem, or it works but I don't know how to implement them.

Thanks

Need Help
  • MFelix's avatar
    MFelix
    7 years ago

    Hi gluizqueiroz ,

     

    As you refer you need to create a table with two lines:

     

    Sold

    Unsold

     

    Then create a measure similar to this:

    FIlter data =
IF (
    COUNT ( 'Sold/Unsold'[Cat] ) > 1;
    1;
    IF (
        MAX ( 'Sold/Unsold'[Cat] ) = "Sold";
        COUNTX (
            FILTER (
                SUMMARIZE ( Sales; Sales[Product]; "Sold"; [Sold/Unsold] );
                [Sold] = "Sold"
            );
            [Sold]
        );
        COUNTX (
            FILTER (
                SUMMARIZE ( Sales; Sales[Product]; "Unsold"; [Sold/Unsold] );
                [Unsold] = "Unsold"
            );
            [Unsold]
        )
    )
)

    Be aware that this measure is based on way to count the rows of products that have sold or unsold, but the resul will be simply 1 per row.

     

    I then used this to filter the table visual to get the final result.

     

    Check PBIX file attach.

     

    Regards,

    MFelix

1 Reply

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    7 years ago

    Hi gluizqueiroz ,

     

    As you refer you need to create a table with two lines:

     

    Sold

    Unsold

     

    Then create a measure similar to this:

    FIlter data =
IF (
    COUNT ( 'Sold/Unsold'[Cat] ) > 1;
    1;
    IF (
        MAX ( 'Sold/Unsold'[Cat] ) = "Sold";
        COUNTX (
            FILTER (
                SUMMARIZE ( Sales; Sales[Product]; "Sold"; [Sold/Unsold] );
                [Sold] = "Sold"
            );
            [Sold]
        );
        COUNTX (
            FILTER (
                SUMMARIZE ( Sales; Sales[Product]; "Unsold"; [Sold/Unsold] );
                [Unsold] = "Unsold"
            );
            [Unsold]
        )
    )
)

    Be aware that this measure is based on way to count the rows of products that have sold or unsold, but the resul will be simply 1 per row.

     

    I then used this to filter the table visual to get the final result.

     

    Check PBIX file attach.

     

    Regards,

    MFelix