Forum Discussion

Mike_Mace's avatar
Mike_Mace
Resolver I
6 years ago
Solved

Filtering multiple curves

Curves below show running values across many companies. 

When trying to filter by company only one of the curves gets filtered as per second snip. In this respect it would take three standalone filters to show per company values.

I've created a relationship on the company column between three curves but it is not working.

The relationship was set as many2many and inactive. 

There is another active relationship on dates. The filtering visual though should activate second relationship??

 

 

 

all companiesfiltering planned only

Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi Mike_Mace ,

     

    Thank you for your so detail relationship picture. We can try to create a calculated table as Dim table

     

    CompanySlicer =
UNION (
    ADDCOLUMNS ( DISTINCT ( 'Actuals'[Company] ), "Type", "Actuals" ),
    ADDCOLUMNS ( DISTINCT ( 'Recovery'[Company] ), "Type", "Recovery" ),
    ADDCOLUMNS ( DISTINCT ( 'Planned'[Company] ), "Type", "Planned" )
)

     

    After create relationship with three fact tables ,then you can put the company column and type column into two slicer(or hieracy slicer).


    Best regards,

     

4 Replies

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

    Hi Mike_Mace ,

     

    Sorry for that, We cannot understand your data model clearly, Could you please describe the fields of each tables ,  the relations between tables simply and the field used in the chart?

    Please don't contain any Confidential Information or Real data in your reply.


    Best regards,

    • Mike_Mace's avatar
      Mike_Mace
      Resolver I
      6 years ago

      Hi v-lid-msft,

       

      Thanks for the message. Info below:

       

      1. describe the fields of each tables 

      There are three sets of deliverables per company. Planned, Actual, Recovery. 

      I use a DAX to measure the Running values.

      Because each set of deliverable uses a different date-set. I use a calendar base date set on the running DAX

      DAX:

      Planned Running =
      CALCULATE(
          SUM('Planned'[Planned]),
          FILTER(
              ALLSELECTED('Date Calendar'[Date Calendar]),
              'Date Calendar'[Date Calendar] <= MAX('Date Calendar'[Date Calendar])
          )
      )

       

      2. the relations between tables simply 

      Yellow highlighted is the active relationship between all date sets. Very much needed to be able to plot all curves against one date set. 

      Blue underlined is my attempt to create another relationship under company

      relationships

       

      3. the field used in the chart?

      The measures for the 3 Running totals are on the chart and a calendar based date set on the X axis.

      Since the Scurves are global, filtering down to company level is needed.

      Snip below extents view to Fields section.

      I used a filter based on Company from Planned table. Filtering on Company 5 filters the planned curve but not the other two which means my attempt to create a relationship on company columns between Planned, Actual, Recovery data was unsuccessful 🙂

       

      Could easily create 3 Filters. One for each Planned, Actual, Recovery company column.

      But that would only confuse users 

       

       

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

        Hi Mike_Mace ,

         

        Thank you for your so detail relationship picture. We can try to create a calculated table as Dim table

         

        CompanySlicer =
UNION (
    ADDCOLUMNS ( DISTINCT ( 'Actuals'[Company] ), "Type", "Actuals" ),
    ADDCOLUMNS ( DISTINCT ( 'Recovery'[Company] ), "Type", "Recovery" ),
    ADDCOLUMNS ( DISTINCT ( 'Planned'[Company] ), "Type", "Planned" )
)

         

        After create relationship with three fact tables ,then you can put the company column and type column into two slicer(or hieracy slicer).


        Best regards,