Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Filter a value based on multiple column or match on multiple columns

Hi there,

 

I'm looking how to achieve the below SQL. Appreciate any suggestion how to achieve the below.

--If filter value is B 

SELECT * FROM TABLE
WHERE COLUMN-1 = 'B' OR COLUMN-2 = 'B'

 

report link

https://github.com/ArtiAshu/dummy.git

 

 

Thanks.

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

     

    You can create a calcualted table which is separate.

    Table 2 = DISTINCT('Table'[AllAirpots])

    No relationship between tables.

    Create a measure for visual level filters.

    Measure =
VAR _sel =
    ALLSELECTED ( 'Table 2'[AllAirpots] )
RETURN
    IF (
        ISFILTERED ( 'Table 2'[AllAirpots] ),
        IF (
            MAX ( 'Table'[Destination] )
                IN _sel
                    || MAX ( 'Table'[Source] ) IN _sel,
            1
        ),
        1
    )

    Put the measure into filters of the table visual. Set up show items when the value is 1.

    The field of the slicer is from Table 2. When you select "B" in the slicer, the result is as follow.

     

                                                                                                                                                             

    Best Regards,

    Stephen Tao

     

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

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    CNENFRNL Thank you very much. I'm afraid the table structure you had created is different from what my report table has. Per my current structure Source and Destination are 2 different columns of the same row. 

     

    Is there anyway other way with the same sample table structure as above? i tried creating distinct of airport tables but still wondering how to get selectedvalue in source or destination and get all the rows.

     

    Thank you.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

     

    You can create a calcualted table which is separate.

    Table 2 = DISTINCT('Table'[AllAirpots])

    No relationship between tables.

    Create a measure for visual level filters.

    Measure =
VAR _sel =
    ALLSELECTED ( 'Table 2'[AllAirpots] )
RETURN
    IF (
        ISFILTERED ( 'Table 2'[AllAirpots] ),
        IF (
            MAX ( 'Table'[Destination] )
                IN _sel
                    || MAX ( 'Table'[Source] ) IN _sel,
            1
        ),
        1
    )

    Put the measure into filters of the table visual. Set up show items when the value is 1.

    The field of the slicer is from Table 2. When you select "B" in the slicer, the result is as follow.

     

                                                                                                                                                             

    Best Regards,

    Stephen Tao

     

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