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.