Forum Discussion

tangerinemdr15's avatar
tangerinemdr15
Helper I
4 years ago
Solved

Filtering on a Matrix

Hi,

 This may be a simple solutuion, I dont use Power BI much. I'll include a sample of the issue. In a matrix, is there any way to only show rows if it has certain criteria? 

Here is the underlying data

 

Here is the matrix:

I want to only show the highlighted ones because it has both alt and reg. 

Thanks in advance. 

 

Need Help
  • PaulDBrown's avatar
    PaulDBrown
    4 years ago

    Ah, I see... But always up for a challenge. Try this as a filter:

    FILTER 2 =
IF (
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Payment Type] ),
        ALLEXCEPT ( 'Table', 'Table'[Claim #] )
    ) = 2,
    1,
    IF ( MAX ( 'Table'[Payment Type] ) = "ALT", 1 )
)

     

7 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    4 years ago

    Here is one way. Create an unrelated table using:

     

    Reference table = SUMMARIZE('Table','Table'[Claim #],'Table'[Payment Type])

     

     

    Create a measure to use as a filter in the visual:

     

    Filter =
VAR _ALT =
    CALCULATETABLE (
        VALUES ( 'Reference table'[Claim #] ),
        FILTER ( 'Reference table', 'Reference table'[Payment Type] = "ALT" )
    )
VAR _ALL =
    VALUES ( 'Table'[Claim #] )
RETURN
    COUNTROWS ( INTERSECT ( _ALT, _ALL ) )

     

    Cretae the visual and add the [Filter] measure to the filter pane for the visual and set the value to 1

     

     

    • tangerinemdr15's avatar
      tangerinemdr15
      Helper I
      4 years ago

      Thank you for your help PaulDBrown . Unfortunately, I cant create a new table because its connected to a live database. I appreciate your time!

      • PaulDBrown's avatar
        PaulDBrown
        Community Champion
        4 years ago

        Ah, I see... But always up for a challenge. Try this as a filter:

        FILTER 2 =
IF (
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Payment Type] ),
        ALLEXCEPT ( 'Table', 'Table'[Claim #] )
    ) = 2,
    1,
    IF ( MAX ( 'Table'[Payment Type] ) = "ALT", 1 )
)

         

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    tangerinemdr15 , Try a measure like

    Sumx(filter(summarize(Table, Table[Claim#], "_sum", sum(Table[Amount]) , "_cnt", calculate(DistinctCount(Table[Payment Type]), filter(Table, Table[Payment Type] in {"REG","ALT"} ))),[_cnt] =2), [_sum])

    • tangerinemdr15's avatar
      tangerinemdr15
      Helper I
      4 years ago

      amitchandak Thank you so much! that was perfect! I realized, though, that I needed to modify my question. what would be the measure for this result? I would like to see only those with ALT and Reg or only ALT.  Sorry!