Forum Discussion

samp19901's avatar
samp19901
Regular Visitor
3 years ago
Solved

Chart Columns directing to different slicers?

As per subject, is there a way of say having 2 slicers (the same), but each one directed at a measure in a chart? for instance, see my screenshot above, i want the light blue column dictated b...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi samp19901 ,

     

    As far as I know, you need to create two unrelated tables for these two slicers to achieve your goal. 

    My Sample:

    Dim Currency Type 1 = VALUES('Table'[Currency Type])
    Dim Currency Type 2 = VALUES('Table'[Currency Type])
    DimDate = 
ADDCOLUMNS(CALENDARAUTO(),"MonthShortName",FORMAT([Date],"MMM"),"Month",MONTH([Date]))

    Relationship:

    Measure:

    Measure1 = 
VAR _SELECT1 =
    SELECTEDVALUE ( 'Dim Currency Type 1'[Currency Type] )
RETURN
    IF (
        ISFILTERED ( 'Dim Currency Type 1'[Currency Type] ),
        CALCULATE (
            SUM ( 'Table'[Rate] ),
            FILTER ( 'Table', 'Table'[Currency Type] = _SELECT1 )
        ),
        CALCULATE ( SUM ( 'Table'[Rate] ) )
    )
    Measure2 = 
VAR _SELECT2 =
    SELECTEDVALUE ( 'Dim Currency Type 2'[Currency Type] )
RETURN
    IF (
        ISFILTERED ( 'Dim Currency Type 2'[Currency Type] ),
        CALCULATE (
            SUM ( 'Table'[Rate] ),
            FILTER ( 'Table', 'Table'[Currency Type] = _SELECT2 )
        ),
        CALCULATE ( SUM ( 'Table'[Rate] ) )
    )

     Result is as below.

    By Default:

    With Selection:

     

    Best Regards,
    Rico Zhou

     

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

     

     

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi samp19901 ,

 

As far as I know, you need to create two unrelated tables for these two slicers to achieve your goal. 

My Sample:

Dim Currency Type 1 = VALUES('Table'[Currency Type])
Dim Currency Type 2 = VALUES('Table'[Currency Type])
DimDate = 
ADDCOLUMNS(CALENDARAUTO(),"MonthShortName",FORMAT([Date],"MMM"),"Month",MONTH([Date]))

Relationship:

Measure:

Measure1 = 
VAR _SELECT1 =
    SELECTEDVALUE ( 'Dim Currency Type 1'[Currency Type] )
RETURN
    IF (
        ISFILTERED ( 'Dim Currency Type 1'[Currency Type] ),
        CALCULATE (
            SUM ( 'Table'[Rate] ),
            FILTER ( 'Table', 'Table'[Currency Type] = _SELECT1 )
        ),
        CALCULATE ( SUM ( 'Table'[Rate] ) )
    )
Measure2 = 
VAR _SELECT2 =
    SELECTEDVALUE ( 'Dim Currency Type 2'[Currency Type] )
RETURN
    IF (
        ISFILTERED ( 'Dim Currency Type 2'[Currency Type] ),
        CALCULATE (
            SUM ( 'Table'[Rate] ),
            FILTER ( 'Table', 'Table'[Currency Type] = _SELECT2 )
        ),
        CALCULATE ( SUM ( 'Table'[Rate] ) )
    )

 Result is as below.

By Default:

With Selection:

 

Best Regards,
Rico Zhou

 

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

 

 

samp19901's avatar
samp19901
Regular Visitor
3 years ago

Thanks Rico! this has helped tremendously!
is there a way from your Measure1, to add additional filtering in there? for instance if i want more than 1 filter to be applied?
thanks

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi samp19901 ,

     

    Yes, you can catch values by SELECTEDVALUE() and add these as conditions in filter part in measure1.

    Measure should look like as below.

    Measure1 =
VAR _SELECT1 =
    SELECTEDVALUE ( 'Dim Currency Type 1'[Currency Type] )
VAR _SELECT2 =
    SELECTEDVALUE ( 'TableName'[Column] )
...
RETURN
    IF (
        ISFILTERED ( 'Dim Currency Type 1'[Currency Type] ),
        CALCULATE (
            SUM ( 'Table'[Rate] ),
            FILTER (
                'Table',
                'Table'[Currency Type] = _SELECT1
                    && 'Table'[Column] = _SELECT2
                    ...
            )
        ),
        CALCULATE ( SUM ( 'Table'[Rate] ) )
    )

     

    Best Regards,
    Rico Zhou

     

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

     

    • samp19901's avatar
      samp19901
      Regular Visitor
      3 years ago

      Hey Rico,

      Is is brilliant, just want i want. thank you so much.

      instead of using : 

      CALCULATE (
            SUM ( 'Table'[Rate] )

       Is there  a way i can use a calculated measure? for example:

       
      = ''CALCULATE(SUM('Report Finance_P&L'[Essbase Value]),'Report Finance_P&L'[P&L Line Item]="Revenue")''



      Thank you.