Forum Discussion

anansrivastava's avatar
anansrivastava
Frequent Visitor
5 years ago
Solved

Advanced filtering. Dependent dropdown using modelling

I have created a dim table filter which is modelled from 2 tables

 

My query for this is - 

 

 

calcProjectNameFilter = DISTINCT(
UNION(
VALUES('Vani_Trends actual'[Project Name]),
VALUES('Vani_Trends2 - planned'[Project Name])
)
)

 

 

 

I now want to create a second filter - phase name that lets me filter the graphs. However, when I do the same process as above, it shows me all the phases but I want to show just the ones in a particular project. 

 

How can I create a filter modelled from two tables AND dependent on the selected project name?

Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    Hi anansrivastava ,

     

    Why you don't combine the phase name column and project column in a filter table?

     

    Filter = DISTINCT(
UNION(
SELECTCOLUMNS('Vani_Trends actual',"Project Name",'Vani_Trends actual'[Project Name],"Phase Name",'Vani_Trends actual'[Phase Name]),
SELECTCOLUMNS('Vani_Trends2 - planned',"Project Name",'Vani_Trends2 - planned'[Project Name],"Phase Name",'Vani_Trends2 - planned'[Phase Name])
))

     

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

     

    Best Regards,

    Dedmon Dai

3 Replies

  • selimovd's avatar
    selimovd
    Most Valuable Professional
    5 years ago

    Hey anansrivastava ,

     

    you have to filter then the values that you are using. So try the following for your table:

    FILTER(VALUES('Vani_Trends actual'[Project Name]), 'Vani_Trends actual'[Project Name]='myFavouriteProject')

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis
     
    • anansrivastava's avatar
      anansrivastava
      Frequent Visitor
      5 years ago

      Dear Selimovd,

       

      Thank you for your reply.

      However, I need to use data modelling as my slicer has 2 filter 2 tables. So something like - 

      calcProjectNameFilter = CALCULATETABLE( DISTINCT(
UNION(
VALUES('Vani_Trends actual'[Project Name]),
VALUES('Vani_Trends2 - planned'[Project Name])
),
ALLSELECTED(calcProjectNameFilter)
))

       

      This didn't work. How can I integrate your solution with mine? 

      • v-deddai1-msft's avatar
        v-deddai1-msft
        Community Support
        5 years ago

        Hi anansrivastava ,

         

        Why you don't combine the phase name column and project column in a filter table?

         

        Filter = DISTINCT(
UNION(
SELECTCOLUMNS('Vani_Trends actual',"Project Name",'Vani_Trends actual'[Project Name],"Phase Name",'Vani_Trends actual'[Phase Name]),
SELECTCOLUMNS('Vani_Trends2 - planned',"Project Name",'Vani_Trends2 - planned'[Project Name],"Phase Name",'Vani_Trends2 - planned'[Phase Name])
))

         

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

         

        Best Regards,

        Dedmon Dai