Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 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_Price | Tax_1 | Tax_2 | Tax_3 | Tax_4 |
Table Visual --
Sale_ID | Final_Price |
1 | 18 |
2 | 80 |
If I click on Unity_Price the it must shows this:
Slicer --
Unity_Price | Tax_1 | Tax_2 | Tax_3 | Tax_4 |
Sale_ID | Final_Price |
1 | 10 |
2 | 40 |
Or if I multiselect items, all taxes for example, it must shows this:
Slicer --
Unity_Price | Tax_1 | Tax_2 | Tax_3 | Tax_4 |
Sale_ID | Final_Price |
1 | 8 |
2 | 40 |
How can I do that?
Thanks.
Solved! Go to Solution.
Hi @Anonymous,
I would solve this using a measure do the following:
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
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português
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!!!
Customer Name | Period | AAA-Volumn | AAA-MS | AAA-Rank | BBB-Volumn | BBB-MS | BBB-Rank | CCC-Volumn | CCC-MS | CCC-Rank |
Smith | Month | 2 | 10% | 3 | 1 | 2% | 2 | 4 | 2% | 2 |
John | Month | 3 | 20% | 2 | 2 | 3% | 3 | 2 | 3% | 2 |
Albert | Month | 4 | 30% | 1 | 3 | 4% | 1 | 3 | 30% | 3 |
April | Month | 5 | 10% | 3 | 4 | 5% | 2 | 2 | 23% | 3 |
Smith | QTR | 4 | 30% | 3 | 3 | 2% | 1 | 1 | 5% | 1 |
John | QTR | 5 | 20% | 2 | 2 | 3% | 3 | 2 | 20% | 3 |
Albert | QTR | 1 | 20% | 1 | 1 | 1% | 2 | 3 | 34% | 2 |
April | QTR | 4 | 10% | 3 | 1 | 2% | 4 | 4 | 5% | 4 |
Smith | YTD | 5 | 10% | 3 | 5 | 5% | 2 | 2 | 2% | 3 |
John | YTD | 7 | 20% | 2 | 2 | 4% | 1 | 3 | 20% | 1 |
Albert | YTD | 2 | 30% | 1 | 3 | 5% | 3 | 2 | 3% | 2 |
April | YTD | 2 | 10% | 3 | 1 | 3% | 1 | 1 | 10% | 3 |
Category Slicer | Customer Name | AAA-Volumn | AAA-MS | AAA-Rank | |
AAA | Smith | 2 | 10% | 3 | |
BBB | John | 3 | 20% | 2 | |
CCC | Albert | 4 | 30% | 1 | |
April | 5 | 10% | 3 | ||
Period Slicer | |||||
Month |
|
Hi @MFelix ,
I want to create a slicer of multiple columns based on another slicer having parameters.
for example raw data is like this:
Event | Student ID | Maths teacher | Science Teacher | English Teacher |
Exhibition | 1 | Ravi Chandra | Spriha Das | Vandana p |
Exhibition | 2 | RK Das | Mamta Chobey | |
Exhibition | 3 | Rajesh Ranjan | Rajesh 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:
Event | Student ID | Maths teacher |
Exhibition | 1 | Ravi Chandra |
Exhibition | 2 | RK 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:
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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.
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()
)
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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português@MFelix Could you please help I wanted to create slicer from column name. Please see my data table.
Id | Age | Boy | Girl | Adult | Child |
1 | 25 | TRUE | FALSE | TRUE | FALSE |
2 | 30 | FALSE | TRUE | TRUE | FALSE |
3 | 12 | TRUE | FALSE | FALSE | TRUE |
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.
Id | Age | Boy | Girl | Adult | Child |
1 | 25 | TRUE | FALSE | TRUE | FALSE |
3 | 12 | TRUE | FALSE | FALSE | TRUE |
Another example: If I select Adult this should show up.
Id | Age | Boy | Girl | Adult | Child |
1 | 25 | TRUE | FALSE | TRUE | FALSE |
2 | 30 | FALSE | TRUE | TRUE | FALSE |
Could you please help?
Thank you
hI @neenae2860 ,
Create a new table with the values you want to filter:
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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @Anonymous,
I would solve this using a measure do the following:
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
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi 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:
CW | Mercedes | BMW | Audi | VW |
1 | 86 | 102 | 108 | 110 |
2 | 96 | 89 | 86 | 100 |
3 | 92 | 83 | 98 | 84 |
4 | 81 | 74 | 76 | 93 |
(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.
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:
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThanks, that was exactly the tipp I needed 👍
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHey @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
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!
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:
BadgeID | Job Title | Country | FirstName | LastName | BoothNumber | Booth Name | Duration Seconds |
123 | Engineer | USA | abc | Woods | 27 | Booth 1 | 600 |
456 | Engineer | Germany | def | Woods | 27 | Booth 1 | 480 |
789 | Engineer | India | ghi | Sa | 27 | Booth 1 | 1320 |
532 | Chief Information Officer | USA | jkl | Va | 14 | Booth 2 | 240 |
1182304 | Operations Manager | USA | Chet | Manchester | 21 | Booth 3 | 980 |
1182523 | Account Manager | USA | Travis | Fulton | 21 | Booth 3 | 1080 |
1182657 | Senior Engineer | India | Luisa | Sangines | 21 | Booth 3 | 1680 |
1182879 | Engineer | USA | Mark | Skallet | 21 | Booth 3 | 480 |
1149995 | Analyst | USA | Megan | Hemmila | 37 | Booth 4 | 1200 |
1149995 | Analyst | India | Megan | Hemmila | 37 | Booth 4 | 2280 |
1150180 | Architect|Partner | India | Hoa | Tram | 37 | Booth 4 | 1200 |
1150180 | Architect|Partner | Germany | Hoa | Tram | 37 | Booth 4 | 480 |
117 | Senior Engineer | USA | pqr | Pa | 14 | Booth 2 | 360 |
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |