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 colum...
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,

     

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!