Forum Discussion

Dunner2020's avatar
Dunner2020
Icon for Post Prodigy rankPost Prodigy
5 years ago
Solved

Enabling filter by check box

Hi there,

 

I have three filter measures (let say filter1, filter2, and filter3). I created a measure that calculates the number of events (let say the name of measure is [no of events]. I want to have a checkbox with the values "Yes" or "No" so that when we ticked the Yes filter measures get enabled and [no of events] shows the number of events after filtering the data. Otherwise (if it's ticked "No") it shows the number of events without filtering the data. Is it possible to do in Power BI?

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Dunner2020 

    I think you can achieve your goal by building a unrelated slicer table and measure.

    Due to I don't know your data model I build a sample table to have a test.

    Sample Table:

    Slicer Table:

    Measure:

    No of Event = 
VAR _Checkbox =
    SELECTEDVALUE ( Checkbox[Status] )
VAR _YES =
    SUMX (
        FILTER (
            'Table',
            'Table'[Category1] = "A"
                && 'Table'[Category2] = "BB"
                && 'Table'[Category3] = "Event1"
        ),
        'Table'[QTY]
    )
VAR _NO =
    SUM ( 'Table'[QTY] )
RETURN
    IF (
        HASONEFILTER ( Checkbox[Status] ),
        IF ( _Checkbox = "Yes", _YES, _NO ),
        ""
    )

    Build a card visual to show the measure result. By default it will show blank, select "No", it will show sum of all QTY without filter, select "Yes", it will show results filtered by filter1 (Category1 = "A"), filter2 (Category2 = "BB") and filter3 (Category3 = "Event1").

    Result is as below.

    Select No:

    Select Yes:

    You can download the pbix file from this link: Enabling filter by check box

     

    Best Regards,

    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

3 Replies

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    5 years ago

    Hey Dunner2020 ,

     

    Regarding your question, I have to admit that I have my problems understanding your requirement.

    Please consider to create a pbix file that contains sample data, but still reflects the data model (https://docs.microsoft.com/en-us/learn/modules/design-model-power-bi/).
    You can use this to enter data directly in Power BI Desktop: https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-enter-data-directly-into-desktop. Upload the pbix file to onedrive or dropbox and share the link. If you are using Excel to create the sample data, share the xlsx as well.
    Create columns that contain the expected results, use the sample data to explain how the result is derived from the sample data.

     

    Regards,

    Tom

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Dunner2020 

    I think you can achieve your goal by building a unrelated slicer table and measure.

    Due to I don't know your data model I build a sample table to have a test.

    Sample Table:

    Slicer Table:

    Measure:

    No of Event = 
VAR _Checkbox =
    SELECTEDVALUE ( Checkbox[Status] )
VAR _YES =
    SUMX (
        FILTER (
            'Table',
            'Table'[Category1] = "A"
                && 'Table'[Category2] = "BB"
                && 'Table'[Category3] = "Event1"
        ),
        'Table'[QTY]
    )
VAR _NO =
    SUM ( 'Table'[QTY] )
RETURN
    IF (
        HASONEFILTER ( Checkbox[Status] ),
        IF ( _Checkbox = "Yes", _YES, _NO ),
        ""
    )

    Build a card visual to show the measure result. By default it will show blank, select "No", it will show sum of all QTY without filter, select "Yes", it will show results filtered by filter1 (Category1 = "A"), filter2 (Category2 = "BB") and filter3 (Category3 = "Event1").

    Result is as below.

    Select No:

    Select Yes:

    You can download the pbix file from this link: Enabling filter by check box

     

    Best Regards,

    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Dunner2020 

    Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

     

    Best Regards,

    Rico Zhou