Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

filter based on another report

Hi, I have a table

Name Type

Messi Football

Messi Cricket

Ronaldo Football

Nemar Football

I want names who only plays Football not both. Messi should not come.

Expected output

Name Type

Ronaldo Football

Nemar Football

Thanks in advance.

 

Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi Anonymous ,

     

    We can try to use the following measure in visual filter to meet your requirement:

     

    Visual Control =
IF (
    CALCULATE ( DISTINCTCOUNT ( 'Table'[Type] ), ALL ( 'Table' ) )
        <> CALCULATE ( DISTINCTCOUNT ( 'Table'[Type] ), ALLSELECTED ( 'Table' ) ),
    IF (
        CALCULATE (
            COUNTROWS ( 'Table' ),
            FILTER (
                CALCULATETABLE ( 'Table', ALL ( 'Table'[Type] ) ),
                'Table'[Type] IN DISTINCT ( 'Table'[Type] )
            )
        )
            = CALCULATE (
                COUNTROWS ( 'Table' ),
                CALCULATETABLE ( 'Table', ALL ( 'Table'[Type] ) )
            ),
        1,
        -1
    ),
    1
)

     


    Best regards,

     

4 Replies

  • MFelix's avatar
    MFelix
    Super User
    6 years ago

    Hi Anonymous ,

     

    This is not a very explicit question but believe that what you need is to create a measure similar to this:

    Filter = CALCULATE(DISTINCTCOUNT('Table'[Type]);ALLEXCEPT('Table';'Table'[Name]))

     

    Than place it on the filter visualization and select where value is equal to 1.

     

    For the future please see this post regarding How to Get Your Question Answered Quickly (courtesy of @Greg_Deckler) and How to provide sample data in the Power BI Forum (courtesy of @ImkeF).

     

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    We can try to use the following measure in visual filter to meet your requirement:

     

    Visual Control =
IF (
    CALCULATE ( DISTINCTCOUNT ( 'Table'[Type] ), ALL ( 'Table' ) )
        <> CALCULATE ( DISTINCTCOUNT ( 'Table'[Type] ), ALLSELECTED ( 'Table' ) ),
    IF (
        CALCULATE (
            COUNTROWS ( 'Table' ),
            FILTER (
                CALCULATETABLE ( 'Table', ALL ( 'Table'[Type] ) ),
                'Table'[Type] IN DISTINCT ( 'Table'[Type] )
            )
        )
            = CALCULATE (
                COUNTROWS ( 'Table' ),
                CALCULATETABLE ( 'Table', ALL ( 'Table'[Type] ) )
            ),
        1,
        -1
    ),
    1
)

     


    Best regards,

     

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?


    Best regards,