Forum Discussion

EshaanG's avatar
EshaanG
Helper I
6 years ago

How to dynamically filter a table in PowerBI

I have a table in a data model which is like this :

 

PersonID   ProgramID   Status

1                P1                Completed

2                P2                Completed

3                                    Not Started

 

I want to represent this in a table visual such that when someone selects ProgramID = P1 in the slicer, the table looks like 

 

PersonID   ProgramID   Status

1                P1                Completed

2                                    Not Started

3                                    Not Started

 

Any help on this will be very useful!?

 

 

Need Help

5 Replies

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

    Hi , EshaanG 

     

     

    If help , please refer to these steps:

    1.create a calculate table as a slicer:

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

     

    2.create measure "Measure 2"  as the field "ProgramID" ,measure"Statue measure " as the field "Status" to apply to the table visual

    Measure 2 = IF(CALCULATE(COUNTROWS('Table'),'Table'[ProgramID] in DISTINCT('Table 2'[ProgramID]))>0,MAX('Table'[ProgramID]),BLANK())
    Status measure =
IF (
    CALCULATE ( COUNTROWS ( 'Table 2' ), ALLSELECTED ( 'Table 2' ) )
        <> CALCULATE ( COUNTROWS ( 'Table 2' ), ALL ( 'Table 2' ) ),
    IF (
        CALCULATE (
            COUNTROWS ( 'Table' ),
            'Table'[ProgramID] IN DISTINCT ( 'Table 2'[ProgramID] )
        ) > 0,
        "Completed",
        "Not Started"
    ),
    MAX ( 'Table'[Status] )
)

     

    it will show as below:

     

    Here is a demo.

    pbix attached

     

    Best Regards,
    Community Support Team _ Eason
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

     

     

     

     

     

     

    • EshaanG's avatar
      EshaanG
      Helper I
      6 years ago

      Hello v-easonf-msft

       

      Thanks for the solution. It works pretty much fine in my actual data but somehow some the program column shows random values even though the final status and row count is accurate!

      Shall I send a .pbix for you to have a look

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

        Hi , EshaanG 

        Please tell me more details.You can upload the expected results or screenshots  .

        It will make it easier for me to understand your question if you can make a sample/ or share your pbix file without any sensitive data to me .

         

        Best Regards,
        Community Support Team _ Eason

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    You will need a disconnected table for your ProgramID values. Use that for your slicer. You can then use SELECTEDVALUE to get the selected value and use that in a measure to return the correct status.