Forum Discussion

POWER_MI's avatar
POWER_MI
Post Patron
5 years ago
Solved

Filter's Data between two table Help

Hi all, 

 

I have some dobt with Power Bi to make and create this functionality. 

There are two tables table 1 and table 2.

Table 1 coloumn is Son Project / GROUPE PROJECT / COULUMN1 /COULUMN2 / COULUMN3

Table 2 coloumn is MOTHER PROJECT 

I need to know if it's possible to filter table 1 data base on table 2 coloumn values. 

For example 

 

If MOTHER PROJECT = _mother1  on table results or on table 1 will take juste 

All rows with Son Project=Proj_soon_mother1 / GROUPE PROJECT / COULUMN1 /COULUMN2 / COULUMN3

 

If MOTHER PROJECT = _mother2  on table results or on table 1 will take juste 

All rows Son Project=....__mother2 / GROUPE PROJECT / COULUMN1 /COULUMN2 / COULUMN3

 

You can find Power Bi project here. Thanks a lot for help

 

Need Help
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    Hi POWER_MI ,

    You can put the version column in the matrix and apply this measure to replace the previous:

    A =
SWITCH (
    SELECTEDVALUE ( Project[Project] ),
    "mother1",
        IF (
            CALCULATE (
                COUNTROWS (
                    FILTER (
                        'Project',
                        CONTAINSSTRING (
                            SELECTEDVALUE ( COLOR_FIELD_TO_ADD[Soon_Projet] ),
                            'Project'[Project]
                        )
                    )
                ),
                FILTER ( 'COLOR_FIELD_TO_ADD', 'COLOR_FIELD_TO_ADD'[Version] = "Version A" )
            ) > 0,
            1
        ),
    "mother2",
        IF (
            CALCULATE (
                COUNTROWS (
                    FILTER (
                        'Project',
                        CONTAINSSTRING (
                            SELECTEDVALUE ( COLOR_FIELD_TO_ADD[Soon_Projet] ),
                            'Project'[Project]
                        )
                    )
                ),
                FILTER ( 'COLOR_FIELD_TO_ADD', 'COLOR_FIELD_TO_ADD'[Version] = "Version B" )
            ) > 0,
            1
        ),
    "mother3",
        IF (
            CALCULATE (
                COUNTROWS (
                    FILTER (
                        'Project',
                        CONTAINSSTRING (
                            SELECTEDVALUE ( COLOR_FIELD_TO_ADD[Soon_Projet] ),
                            'Project'[Project]
                        )
                    )
                ),
                FILTER ( 'COLOR_FIELD_TO_ADD', 'COLOR_FIELD_TO_ADD'[Version] = "Version C" )
            ) > 0,
            1
        )
)

    Attached the file in the below, hopes to help you.

     

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

9 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    5 years ago

    POWER_MI 

    Try:

    Filter criteria =
    VAR _Table1 = COUNROWS(

    CALCULATETABLE(VALUES(Table2[Son Project]), table2[Son Project] IN {proj_soon-mother1})

     

    VAR _Table2 = COUNROWS(

    CALCULATETABLE(VALUES(Table2[Son Project]), table2[Son Project] IN {proj_soon-mother2})

     

    RETURN

    IF(SELECTEDVALUE(Table1) = "_mother1", _Table1, _Table2)

     

    1) add a alicer to the page with the field from  the MOTHER PROJECT

    2) create a table/matrix visual with all columns from table 1 and add the [Filter Criteria] measure to the visual

     

  • v-yingjl's avatar
    v-yingjl
    Community Support
    5 years ago

    Hi POWER_MI ,

    Based on your description, you can create this measure, put it in the table visual filter and set its value as 1:

    control = 
IF (
    COUNTROWS (
        FILTER (
            'Tableau1',
            CONTAINSSTRINGEXACT (
                SELECTEDVALUE ( Tableau2[Son Project] ),
                'Tableau1'[MOTHER PROJECT ]
            )
        )
    ) > 0,
    1,
    0
)

    Note: Table visuals will not show the repeated rows.

    Attached a sample file in the below, hopes to help you.

     

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

    • POWER_MI's avatar
      POWER_MI
      Post Patron
      5 years ago

      Hi all,

      v-yingjl Thanks a lot for answer. In exercise it's working fine but when i added all calculation of project it's doe not work. 

      Is it possible to propose solution with Join relation between two table. 

      Because this is first example but on Tableau 2 table will add other coloumn Home/Color/ID so if with join it's simple to add,

      so is it possible to update project with Join relation?

      thanks

       

  • POWER_MI's avatar
    POWER_MI
    Post Patron
    5 years ago

    Hi all, 

    v-yingjl I have updated the project with details explained on last message. 

    This is the project with other application so you will see that it's not working

     

    Project; this link 

    • v-yingjl's avatar
      v-yingjl
      Community Support
      5 years ago

      Hi POWER_MI ,

      Based on your sample file, there are three tables and two slicers in the report, what is your expected output, could you please consider sharing more details about it?

      If add relationships to tables, what is the related field, [column] and [level]?

       

      Best Regards,
      Community Support Team _ Yingjie Li

      • POWER_MI's avatar
        POWER_MI
        Post Patron
        5 years ago

        Hi All,

         

        Hello v-yingjl  

        The Table COLOR Field is the Global Table.
        Table Is used to define Color of Rows on dashboard based on criteria level AND color field[R1, R2....]
        SELECTEDVALUE( 'Table'[Level] ) = "A1", CALCULATE([Measure1], COLOR_FIELD_TO_ADD[Coulumn] IN { "R1", "R2", "R31" }),

        So This option is Just to Make color on Table results.

        And Now it's added to the Update of selection of control mesure on this same project so means :
        For color we can keep it and we just add filter to select Soon project based on Mother project ( Table 'Project).
        Hope that it's clear.
        When adding control mesure and filter as you explaned No any results ?

        Thanks in advance

        Project; this link