Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Slicer by columns name

Hello,

 

I have the following table:

 

 

Sale_IDUnity_PriceTax_1Tax_2Tax_3Tax_4
11051584
240302587


To calculate the final price of a sale I created a calcualted column with the following formula:

 

Final_Price = Unity_Price + Tax_1 + Tex_2 - Tax_3 - Tax_4

 

Now I want to filter the final price by my columns names:

 

Slicer -- 

Unity_PriceTax_1Tax_2Tax_3Tax_4

 

Table Visual --

Sale_IDFinal_Price
118
280

 

If I click on Unity_Price the it must shows this:

 

Slicer -- 

Unity_PriceTax_1Tax_2Tax_3Tax_4

 

Sale_IDFinal_Price
110
240

 

Or if I multiselect items, all taxes for example, it must shows this:

 

Slicer -- 

Unity_PriceTax_1Tax_2Tax_3Tax_4

 

Sale_IDFinal_Price
18
240

 

 

How can I do that?

Thanks.

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

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. slicer.png
  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:

resut.png

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

32 REPLIES 32
Anonymous
Not applicable

@MFelix 

 

I saw your post and wonder if you could help me as well 😁

 

I am also trying to create a category slicer based on the Column name,  each category(product) has 3 sub-columns, and they are the same product.

Like, Volumn, MS, Rank, all same 3 columns for each product AAA, BBB, CCC.  

 

I want to create a slicer to filter on the product name AAA, BBB, CCC, so that each time when the user click on each product, together with other row slicer (like period in this example),  a different table will appear. 

Thanks in advance!!!

2023-02-10_11-40-52.png

Customer NamePeriodAAA-VolumnAAA-MSAAA-RankBBB-VolumnBBB-MSBBB-RankCCC-VolumnCCC-MSCCC-Rank
SmithMonth210%312%242%2
JohnMonth320%223%323%2
AlbertMonth430%134%1330%3
AprilMonth510%345%2223%3
SmithQTR430%332%115%1
JohnQTR520%223%3220%3
AlbertQTR120%111%2334%2
AprilQTR410%312%445%4
SmithYTD510%355%222%3
JohnYTD720%224%1320%1
AlbertYTD230%135%323%2
AprilYTD210%313%1110%3

 

Category Slicer Customer NameAAA-VolumnAAA-MSAAA-Rank
AAA Smith210%3
BBB John320%2
CCC Albert430%1
  April510%3
Period Slicer     
Month    

 

Smitaa
New Member

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.

Hi @Smitaa

 

For this you can use the field parameter option:

 

https://learn.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters

 

For the first slicer you select the values from this parameters and for the slicers with the values you use the new functionality:

https://powerbi.microsoft.com/pt-pt/blog/power-bi-november-2022-feature-summary/#post-21321-_Toc1175...


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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.

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:

MFelix_0-1671642890513.png

PBIX file attach.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



neenae2860
Frequent Visitor

@MFelix  Could you please help I wanted to create slicer from column name. Please see my data table.

IdAgeBoyGirlAdultChild
125TRUEFALSETRUEFALSE
230FALSETRUETRUEFALSE
312TRUEFALSEFALSETRUE

This is my data table. I wanted to create slicer which have Boy,Girl,Adult and Child as filter.

Consider to show only true condition and ignore false condition.

 

For example once I select Boy. This should show up.

IdAgeBoyGirlAdultChild
125TRUEFALSETRUEFALSE
312TRUEFALSEFALSETRUE

 

 

 Another example: If I select Adult this should show up.

IdAgeBoyGirlAdultChild
125TRUEFALSETRUEFALSE
230FALSETRUETRUEFALSE

Could you please help?

 

Thank you

hI @neenae2860 ,

 

Create a new table with the values you want to filter:

MFelix_0-1668676505908.png

I call this table Slicer.

Now add the following measure:

Filter measure = SWITCH(SELECTEDVALUE(Slicer[Cat]),
                    "Adult", COUNTROWS(FILTER('Table', 'Table'[Adult] = TRUE())),
                    "Boy", COUNTROWS(FILTER('Table', 'Table'[Boy] = TRUE())),
                    "Girl", COUNTROWS(FILTER('Table', 'Table'[Girl] = TRUE())),
                    "Child", COUNTROWS(FILTER('Table', 'Table'[Child] = TRUE()))
                    )

Add this measure has a filter on the visualization you need and set it to is not blank.

 

MFelix_1-1668676571653.pngMFelix_2-1668676580450.png

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



@MFelix Thank you so much. This really helped me.

MFelix
Super User
Super User

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. slicer.png
  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:

resut.png

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi all,

 

I have a somehow similar issue. I need a filter or slicer where I can choose the column (single or multiple) names which data should be shown in the diagram.

 

Following data table:

CWMercedesBMWAudiVW
186102108110
2968986100
392839884
481747693

(CW=calender week)

 

I want to create a line chart, where the CW is in the X-axis and in Y-axis I have the values from Mercedes, Audi etc..(see below)

In Slicer/Filter I want to be able to select one or multiple of the categories (column names), which will be shown then in the line chart or table visualisation.

 

knst24_0-1710858789259.png

 

It would be great if somebody could give me an easy solution, because I have 53 different columns and 42 calender weeks in my table  🙂

 

HI @knst24 ,

 

Based on the image you are showing the best option is to unpivot you data to get a single column for values and a single colunm for the brands something similar to this:

 

MFelix_0-1710859639111.png

 

Full code for Power Query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RYyxDcAwCAR3+dqFia0YZkHsv0ZypEjzOnH/ZMo05PcbNq9OJ22qRopLID3+2qcWCu9NrHy32SBvD3QYxVLVAw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CW = _t, Mercedes = _t, BMW = _t, Audi = _t, VW = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CW", Int64.Type}, {"Mercedes", Int64.Type}, {"BMW", Int64.Type}, {"Audi", Int64.Type}, {"VW", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"CW"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"

The you can use values on Y Axis and the Attribute on the legend.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Thanks, that was exactly the tipp I needed 👍

Anonymous
Not applicable

Hi Miguel

 

I am trying to achieve the same thing with Strings instead of Taxes

 

Please see the below sample code and assist me on the same 

 

This is what i tried but not succesful 

 

Verification Type =
VAR ThirdPartyAudit =
IF (
CONTAINS ( Slicer, Slicer[Selection], "abcd" ) = TRUE (),
"abcd" ),
0
)
VAR InCountryVisit =
IF (
CONTAINS ( Slicer, Slicer[Selection], "efgh" ) = TRUE (),
"efgh",
0
)
VAR Remote =
IF (
CONTAINS ( Slicer, Slicer[Selection], "pqrs") = TRUE (),
"pqrs",
0
)
VAR STP=
IF (
CONTAINS ( Slicer, Slicer[Selection], "xyz" ) = TRUE (),
"xyz",
0
)
RETURN
"what should i write here ?"

Hi @Anonymous,

 

In this case and since you are using text you need to use the & between the several values instead of the +.

 

However be advise that if you are using text you don't want to return 0 if the value does not exists maybe you should change it to blank.  Check the code below.

 

Verification Type =
VAR ThirdPartyAudi =
IF (
CONTAINS ( Slicer, Slicer[Selection], "abcd" ) = TRUE (),
"abcd" ),BLANK() 
)
VAR InCountryVisit =
IF (
CONTAINS ( Slicer, Slicer[Selection], "efgh" ) = TRUE (),
"efgh",BLANK() 
)
VAR Remote =
IF (
CONTAINS ( Slicer, Slicer[Selection], "pqrs") = TRUE (),
"pqrs",
BLANK() 
)
VAR STP=
IF (
CONTAINS ( Slicer, Slicer[Selection], "xyz" ) = TRUE (),
"xyz",
BLANK() 
)
RETURN
Third Party Audit & InCountryVisit & Remote & STP


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Thanks @MFelix 

 

However I am not getting any values in my column

Hi @Anonymous 

 

What is the format of your data? Do you have a table with the text or you just simply want to to a concatenation of the text based on the slicer selections?

 

If it's the last one just do 

CONCATANEX(Slicer, Slicer[Selection], ",")

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Hey @MFelix 

 

No concatenation Required, 
Say i have 4 cols - the one i have used above 

 

IF of them have text values as Yes/No

 

Which ever is 'YES' of the 4, i need to show it as Verification Type 

Anonymous
Not applicable

@MFelix 

I have the following Master table where "Monday7","Tuesday7","Monday30","Tuesday30" represent the Utilization percentage of corresponding product name. For example: "Monday30" represent the utilization percentage of "Water Purifier" of all Mondays in past 30 days.

 

Similarly there are 4 categories (7 days, 30 days, 60 days, 90 days) where for each category, a separate columns will be present for all days of the week.

 

Name               Monday7      Tuesday7       Monday30    Tuesday30
Water Purifier       43                 44                   64                  32
Refrigerator          54                 12                   32                  45


Now i need to create two slicers, one to select the category ( "7 days","30 days", "60 days", "90 days") and second one to select the day of the week ("Mon","Tue","Wed","Thur","Fri","Sat", "Sund")

 

Now once users selects an option from both the slicers, I wanted to show all the visualizations in that page with the following data where the new column Utilization percent should show the data same as "Monday7" column if user selects "7 days" and "Monday" in slicer.

 

Name              UtilizationPercent
Water Purifier            43
Refrigerator               54


Is this possible? can you please guide me?

Can't thank you enough for this solution! Worked for me perfectly!

Anonymous
Not applicable

Hi @MFelix I have similar issue and i want to create a slicer where column name will be slicer and when i click on slicer it should change the data in teh donut. 

 

Please see the screenshot below where is have shown my whole data. I am not sure where to upload PBIx file and attaching screeshot here.. Here is the table:

BadgeIDJob TitleCountryFirstNameLastNameBoothNumberBooth NameDuration Seconds
123EngineerUSAabcWoods27Booth 1600
456EngineerGermanydefWoods27Booth 1480
789EngineerIndiaghiSa27Booth 11320
532Chief Information OfficerUSAjklVa14Booth 2240
1182304Operations ManagerUSAChetManchester21Booth 3980
1182523Account ManagerUSATravisFulton21Booth 31080
1182657Senior EngineerIndiaLuisaSangines21Booth 31680
1182879EngineerUSAMarkSkallet21Booth 3480
1149995AnalystUSAMeganHemmila37Booth 41200
1149995AnalystIndiaMeganHemmila37Booth 42280
1150180Architect|PartnerIndiaHoaTram37Booth 41200
1150180Architect|PartnerGermanyHoaTram37Booth 4480
117Senior EngineerUSApqrPa14Booth 2360

 

 

Slicer by Column Name.png

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.