Forum Discussion

pakovic's avatar
pakovic
Helper I
7 years ago
Solved

group by project

Hi, I'm new in Powerbi. I try to learn, but I would like you to help me 

- I have this table: with 4 projects (A1, A2, A3, A4)

- Each project has some phases (100, 200, 300... etc)

I would like if I filter by OK, only show the projects that are completely OK and If I filter by pending show the projects that are not completed

Here you can download the pbix https://drive.google.com/open?id=1xPP54PcG_2Zg-RyJn1YWfc0REK8NrkRv

 

 

 

I would like group by ID

Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    hi, pakovic 

    After my research, you could try this way:

    Step1:

    Add an individual that only contains “OK” and "Pending" and use it as a slicer

    Step2:

    Create this measure and drag it into visual level filter

    filterresult = 
IF (
    ISFILTERED ( 'Table'[Result] ),
    SWITCH (
        TRUE (),
        SELECTEDVALUE ( 'Table'[Result] ) = "OK", IF (
            CALCULATE (
                COUNTA ( Tabla1[Result] ),
                FILTER ( ALLEXCEPT ( Tabla1, Tabla1[id] ), Tabla1[Result] = "OK" )
            )
                = CALCULATE ( COUNTA ( Tabla1[id] ), ALLEXCEPT ( Tabla1, Tabla1[id] ) ),
            1,
            2
        ),
        SELECTEDVALUE ( 'Table'[Result] ) = "Pending", IF (
            ISBLANK (
                CALCULATE (
                    COUNTA ( Tabla1[Result] ),
                    FILTER ( ALLEXCEPT ( Tabla1, Tabla1[id] ), Tabla1[Result] = "Pending" )
                )
            ),
            2,
            1
        )
    ),
    1
)

    and set filter is 1

    Result:

    and here is pbix file, please try it.

     

    Best Regards,

    Lin

2 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    7 years ago

    hi, pakovic 

    After my research, you could try this way:

    Step1:

    Add an individual that only contains “OK” and "Pending" and use it as a slicer

    Step2:

    Create this measure and drag it into visual level filter

    filterresult = 
IF (
    ISFILTERED ( 'Table'[Result] ),
    SWITCH (
        TRUE (),
        SELECTEDVALUE ( 'Table'[Result] ) = "OK", IF (
            CALCULATE (
                COUNTA ( Tabla1[Result] ),
                FILTER ( ALLEXCEPT ( Tabla1, Tabla1[id] ), Tabla1[Result] = "OK" )
            )
                = CALCULATE ( COUNTA ( Tabla1[id] ), ALLEXCEPT ( Tabla1, Tabla1[id] ) ),
            1,
            2
        ),
        SELECTEDVALUE ( 'Table'[Result] ) = "Pending", IF (
            ISBLANK (
                CALCULATE (
                    COUNTA ( Tabla1[Result] ),
                    FILTER ( ALLEXCEPT ( Tabla1, Tabla1[id] ), Tabla1[Result] = "Pending" )
                )
            ),
            2,
            1
        )
    ),
    1
)

    and set filter is 1

    Result:

    and here is pbix file, please try it.

     

    Best Regards,

    Lin

    • pakovic's avatar
      pakovic
      Helper I
      7 years ago

      oh! my god, Yes this is the best solution. Thank you very much