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