Forum Discussion

tejasruparel's avatar
tejasruparel
Frequent Visitor
6 years ago
Solved

Filter scatter chart by query

Hello,

Say I have the following data:

Query_1

  • column_1
  • column_2

Query_2

  • column_1
  • column_2

Query_N

  • column_1
  • column_2

 

On my report, I have a scatter chart w/ column_1 on X and column_2 on Y from all queries.

(How) Can I filter the displayed query (column_1 vs column_2) using a slicer?

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

    Hi tejasruparel ,

     

    We can created a calculated table and a measure to meet your requirement:

     

    Calculated Table:

     

    Column1_Table = 
UNION (
    ADDCOLUMNS ( DISTINCT ( 'Query_1'[Date] ), "Query_Name", "Query_1" ),
    ADDCOLUMNS ( DISTINCT ( 'Query_2'[Date] ), "Query_Name", "Query_2" ),
    ADDCOLUMNS ( DISTINCT ( 'Query_3'[Date] ), "Query_Name", "Query_3" )
)

     

    Measure:

     

    Column2 =
SUMX (
    'Column1_Table',
    SWITCH (
        [Query_Name],
        "Query_1", CALCULATE ( SUM ( 'Query_1'[Value] ), RELATEDTABLE ( 'Query_1' ) ),
        "Query_2", CALCULATE ( SUM ( 'Query_2'[Value] ), RELATEDTABLE ( 'Query_2' ) ),
        "Query_3", CALCULATE ( SUM ( 'Query_3'[Value] ), RELATEDTABLE ( 'Query_3' ) )
    )
)

     

     


    If it doesn't meet your requirement, Could you please show the exact expected result based on the Tables that we have shared.


    Best regards,

     

3 Replies

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

    Hi tejasruparel ,

     

    We can created a calculated table and a measure to meet your requirement:

     

    Calculated Table:

     

    Column1_Table = 
UNION (
    ADDCOLUMNS ( DISTINCT ( 'Query_1'[Date] ), "Query_Name", "Query_1" ),
    ADDCOLUMNS ( DISTINCT ( 'Query_2'[Date] ), "Query_Name", "Query_2" ),
    ADDCOLUMNS ( DISTINCT ( 'Query_3'[Date] ), "Query_Name", "Query_3" )
)

     

    Measure:

     

    Column2 =
SUMX (
    'Column1_Table',
    SWITCH (
        [Query_Name],
        "Query_1", CALCULATE ( SUM ( 'Query_1'[Value] ), RELATEDTABLE ( 'Query_1' ) ),
        "Query_2", CALCULATE ( SUM ( 'Query_2'[Value] ), RELATEDTABLE ( 'Query_2' ) ),
        "Query_3", CALCULATE ( SUM ( 'Query_3'[Value] ), RELATEDTABLE ( 'Query_3' ) )
    )
)

     

     


    If it doesn't meet your requirement, Could you please show the exact expected result based on the Tables that we have shared.


    Best regards,

     

    • tejasruparel's avatar
      tejasruparel
      Frequent Visitor
      6 years ago

      Hello,

      Thank you so much for your response. 

      Using your approach I am able to filter my scatter chart by queries. However, if I select "Query_1" and "Query_2" on the slicer, I see a sum of respective values. Instead I'd like two distinct scatters - one for "Query_1" and another for "Query_2" within the same visual. Can this be done?

      Thanks!