Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Slicer by columns name

Hello,   I have the following table:     Sale_ID Unity_Price Tax_1 Tax_2 Tax_3 Tax_4 1 10 5 15 8 4 2 40 30 25 8 7 To calculate the final price of a sale I created a...
Need Help
Show and Tell
Tutorial Requests
  • MFelix's avatar
    MFelix
    9 years ago

    Hi Anonymous,

     

    I would solve this using a measure do the following:

    1. Create a table (do not related this with any other tables with the following structure:
      1. Name Slicer - Selection: Unity_Price, Tax_1, Tax_2, Tax_3, Tax_4
    2. Add the following measure to your data table:
    3. Final_Price =
VAR Unity_price =
    IF (
        CONTAINS ( Slicer, Slicer[Selection], "Unity_Price" ) = TRUE (),
        SUM ( Sales[Unity_Price] ),
        0
    )
VAR Tax1 =
    IF (
        CONTAINS ( Slicer, Slicer[Selection], "Tax_1" ) = TRUE (),
        SUM ( Sales[Tax_1] ),
        0
    )
VAR Tax2 =
    IF (
        CONTAINS ( Slicer, Slicer[Selection], "Tax_2" ) = TRUE (),
        SUM ( Sales[Tax_2] ),
        0
    )
VAR Tax3 =
    IF (
        CONTAINS ( Slicer, Slicer[Selection], "Tax_3" ) = TRUE (),
        SUM ( Sales[Tax_3] ),
        0
    )
VAR Tax4 =
    IF (
        CONTAINS ( Slicer, Slicer[Selection], "Tax_4" ) = TRUE (),
        SUM ( Sales[Tax_4] ),
        0
    )
RETURN
    Unity_price + Tax1
        + Tax2
        - Tax3
        - Tax4
    4. Now just add the valuies from the table you created to a slicer and your measure to the table should give the expected result:

     

    Regards,

    MFelix

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hey Meflix,

 

I've almost similar issue but I'm not able to resolve it the way you mentioned.

Following are the steps

  • Slicer with four different column names
  • Select a column name
  • Divide the values in column with values in another column present in data
  • Use this measure on the graph

I tried following the code you mentioned but instead of Sum(Sales[VAR]) I want Divide(VAR, Column Name)

The code is returning a lot of errors

 

ETA_Quality =
VAR On_Time =
IF (
CONTAINS ( Slicer, Slicer[V_Selection], "On Time" ) = TRUE (),
Divide( 'Volvo ETA'[On-Time], 'Volvo ETA'[Total], Blank()),
0
)
VAR Early =
IF (
CONTAINS ( Slicer, Slicer[V_Selection], "Early" ) = TRUE (),
Divide( 'Volvo ETA'[Early], 'Volvo ETA'[Total], Blank()),
0
)
VAR Late =
IF (
CONTAINS ( Slicer, Slicer[V_Selection], "Late" ) = TRUE (),
Divide( 'Volvo ETA'[Late], 'Volvo ETA'[Total], Blank()),
0
)
VAR Cancelled =
IF (
CONTAINS ( Slicer, Slicer[V_Selection], "Cancelled" ) = TRUE (),
CALCULATE( 'Volvo ETA'[Cancelled] 'Volvo ETA'[Total], Blank()),
0
) Return Cancelled+Early+Late+On_Time

 

Also instead of last Return Cancelled+Early+Late+On_Time, I just want to Return all of them which will eventually display the VAR value based on Column Selection.

 

Thanks!

MFelix's avatar
MFelix
Icon for Super User rankSuper User
8 years ago

Hi Anonymous,

 

When you use measure they are calculated based on context so you cannot make the calculations based on a single value from a colum, meaning that when you have 

VAR On_Time =
IF (
CONTAINS ( Slicer, Slicer[V_Selection], "On Time" ) = TRUE (),
Divide( 'Volvo ETA'[On-Time], 'Volvo ETA'[Total], Blank()),
0
)

You should have something like this:

 

VAR On_Time =
IF (
    CONTAINS ( Slicer, Slicer[V_Selection], "On Time" ) = TRUE (),
    DIVIDE ( SUM ( 'Volvo ETA'[On-Time] ), SUM ( 'Volvo ETA'[Total] ), BLANK () ),
    0
)

In this way you are aggregating the values, however be carefull because this can sometimes based on context not return the expected result.

 

Can you please share some sample data and show the expected result in that way I can adjust the formula better.

 

Regards,

MFelix

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hey MFelix ,

     

    Thanks for the reply. I realised the issue now and almost fixed it. Also what are you tryiig to say in the first step? I created a different table with all the four columns in it named Early, Late, Cancelled, On Time but I cannot still add the slicer which gives me all column names in the selection. Can you explain the first step a little more? 

     

    It's giving me this error: 

    Failed to resolve name 'Slicer'. It is not a valid table, variable, or function name.

     

    Thanks!

    Niket

    • MFelix's avatar
      MFelix
      Icon for Super User rankSuper User
      8 years ago

      Hi Anonymous,

       

      In the previous post the name of the table was Slicer that's why it's in the formula.

       

      You need to place the name of the table you used to make the slicer selection.

       

      Regards,

      MFelix

  • Smitaa's avatar
    Smitaa
    New Member
    3 years ago

    Hi MFelix,

    I want to create a slicer of multiple columns based on another slicer having parameters.

    for example raw data is like this:

    EventStudent IDMaths teacherScience TeacherEnglish Teacher
    Exhibition1Ravi ChandraSpriha DasVandana p
    Exhibition2RK Das Mamta Chobey
    Exhibition3 Rajesh RanjanRajesh Ranjan

     

    So first slicer should have values like Maths, Science and English:

    Slicer 1
    Maths
    Science
    English

     

    And second slicer should filter based on Slicer 1 for example if Maths is selected in slicer 1 , then slicer 2 should have following values:

    Slicer 2
    Ravi Chandra
    RK Das

     And table should show this:

    EventStudent IDMaths teacher
    Exhibition1Ravi Chandra
    Exhibition2RK Das

     

    I tried with field parameters but it cant be used in report server

    Please help me with this.