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

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.

    • Smitaa's avatar
      Smitaa
      New Member
      3 years ago

      Hi MFelix,
      Thank you for the reply.

      I tried that option but report with field parameter can't be uploaded to powerbi report server with that version.
      If there is any other way, pls let me know.

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

        Hi Smitaa ,

         

        My bad did not read the last line of your post (😔), for this you need to take a different approach.

         

        • Create a table with the values of the columns and the name of each one this can be created using Power Query or DAX:

         

        Power Query

        let
    Source = Table.Combine( { Table.AddColumn( Table.FromList(Events[Maths teacher], Splitter.SplitByNothing(), null, null, ExtraValues.Error), "Category", each "Math"),
 Table.AddColumn( Table.FromList(Events[Science Teacher], Splitter.SplitByNothing(), null, null, ExtraValues.Error), "Category", each "Science"),
 Table.AddColumn( Table.FromList(Events[English Teacher], Splitter.SplitByNothing(), null, null, ExtraValues.Error), "Category", each "English")
}

),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Column1] <> " ")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Column1", "Teacher"}})
in
    #"Renamed Columns"

         

        DAX

         

        Teacher DAX = 
        FILTER (
            UNION( 
                ADDCOLUMNS(DISTINCT(SELECTCOLUMNS(Events,"Teacher",Events[Maths teacher])), "Category", "Math"),
                ADDCOLUMNS(DISTINCT(SELECTCOLUMNS(Events,"Teacher",Events[Science Teacher])), "Category", "Science"),
                ADDCOLUMNS(DISTINCT(SELECTCOLUMNS(Events,"Teacher",Events[English Teacher])), "Category", "English")
            ),
            [Teacher] <> BLANK()
        )
        •  Add the following measure:
        Filter = 
CALCULATE (
    COUNTROWS ( Events ),
    Events[Maths teacher]
        IN DISTINCT( 'Teacher DAX'[Teacher] )
            || Events[Science Teacher]
                IN DISTINCT( 'Teacher DAX'[Teacher] )
                    || Events[English Teacher] IN DISTINCT(  'Teacher DAX'[Teacher] )
)

         

        Use this measure has a filter on the table and select is not blank and the previous columns on the slicers:

        PBIX file attach.