Perfect, thanks a lot! One additional idea from my side. In case you would like to combine year and month automatically, e.g. to avoid manual adjustments in your page headers, you can also include such measures into the text fields. Here is an example, including the "what if you'd select all month" solution from your slicer. IF([FilterMonthNumber]="", COMBINEVALUES( " ", ""&RIGHT([FilterYear],2), "."&"Full Year" ), COMBINEVALES( " ", ""&RIGHT([FilterYear],2), "."&[FilterMonthNumber] ) )
excellent video, i have added dynamic natural language like titles to many of my reports from your guidance. Quick question, what should I do to show " Bluetooth Headphones, Cellphones & Computers". I'm trying to have it use natural language verbiage whereby if more than 1 is selected, there is an & between the last and second last selection and a comma in all other instances before. anyhelp here would be much appreciated!
Hello Bas, great video. I am wondering if there is a way to get in your measure, multiple selected values from a year slicer such as: whenever the year are consecutive, then the minyear-maxyear would be returned for that interval, and concatenated with other selected years that are outside of the interval. For example, imagine i have the range of years 2010-2023 in my year slicer. consider that the user has selected the years, 2012, 2013, 2014, 2017, 2019, 2020, 2021 then the measure should return 2012-2014, 2017, 2019-2021 Thanks
Thanks for the clarity on this topic. It seems we can't use the new field parameter SELECTEDVALUE to dynamically change the corresponding visual title. Is there a way to make this work?
@HowtoPowerBI - Thanks for the wonderful video. I'm using your "SWITCH between PERCENTAGE and ABSOLUTE VALUES" conditional group measure here to show Sales # when i select absolute value and Sales% when i select percentage value. Chart title is changing as Sales# if i select absolute value, but if i select percentage value chart is not loading the data with error "This type of text cannot be converted as numeric. Can you please help.
Hello Bas, i am trying to apply the instructions you gave in the video on a slicer that was created from Field Parameters, however i am getting the following error: MdxScript(Model) (48,14) Column['column_name'] is part of composite key, but not all columns of the composite key are included in the expression or its dependent expression. Does your method works on slicers created by Modeling-> Modeling -> Fields
When you reference the first column of a fields parameter inside of a selectvalue function you get that error… instead use MAX() or alternatively use selectedvalue on another column (can be calculated column)
Great video! Do you have any tips for how you would handle displaying the selection when the selected value in the filter is "greater than 30" for example?
Another superb video from Bas. Was not aware of that COMBINEVALUES technique at minute 11. If you get a chance, would you mind attaching the PBIX file to your comments section. Thank you!
Hi Bas, many thanks, it's a pleasure following your tips. Is there a way to use a selected value from a graph instead of the value from the slicer? Again, thanks and best regards
Hey Bas, I have a question for you. Have you ever managed to get this working successfully with a Hierarchy in the slicer? When I select the lowest level of the slicer (in my case store names) the measure works. However, if I select a level higher (region - which automatically selects the stores) the filter doesn't work. Any idea how I may be able to fix this? Thanks for the video, was exactly what I was looking for a couple months ago!
Solved. I added multiple ISFILTERED('table'[column]) representing each level of the hierarchy with an or "||." Here is the top part of the measure for anyone wants to see it. VAR __DISTINCT_VALUES_COUNT = DISTINCTCOUNT('Date'[Fiscal W#]) VAR __MAX_VALUES_TO_SHOW = 13 RETURN IF( ISFILTERED('Date'[Fiscal Year Name]) || ISFILTERED('Date'[Fiscal QTR Short Name]) || ISFILTERED('Date'[Fiscal Period Short Name]) || ISFILTERED('Date'[Fiscal W#]), IF( __DISTINCT_VALUES_COUNT > __MAX_VALUES_TO_SHOW,
How does this work for a heirarchy level? If you choose the 2 lines symbol to drill down a whole hierarchy level, the title changes automatically. But if you customize the automatic title, it wont work anymore. Which DAX measure can I use to change the title, so that it shows the selected field name of that hierarchy level?
I need to include an explanatory note in the reports. Does anyone have any ideas on how to do that? Each graph needs to have an analysis note, along with the option to show and hide this note.
Hi BAs, really enjoy your videos and they have enhanced my reports immensely. Is it possible to highlight or colour specific words or numbers in a dynamic title?
Hi First of all thank you very much for this amazing content. Please keep doing the good work man. Just one issue , i am not able to implement the " All Skills" concept< please help !!!!!!!
what if I want to display the previous year on a card with the title "Sales for 2018" if I selected 2019 from the year slicer? how do i get to change the header to calculate "-1"?
If I don't select anything in dynamic filter it should give me slicer header.. How to achieve... I'm getting dynamic filter by category but in select all I'm facing issues
I used the concatenated version of the measure to be able to select multiple items. I am facing an issue where if I have all multiple selected, the text box reflects that value, but when I deselect an item, it does not remove the name from the textbox. Example: Slicer has the following selections: Apples Bananas Watermelon Text box shows: "Apples, Bananas, Watermelon" Slicer changes selection to be: Apples Watermelon Text box shows: "Apples, Bananas, Watermelon" Any idea how to fix this? Thanks
Hi , Can you create a table as per slicer selection .. consider I'd column in a rows like 1,2,3,4. And a slicer using this I'd column.. now if I select I'd from slicer 3 then a table will be created which has all data with I'd 3. Please let me know if requirement is not clear
Hey Bas, really explained well. Just want to solve one problem, for YTD values of Year/Quarter/Months selections I want to keep showing only latest Year/Quarter/Months in dynamic Title though multiple selections applied, Please help!!
Superb. Can something like this be done on the header of measures in a matrix?? I have a measure that is linked to a slicer. Options are LBE1 to LBE4. Depending on the choice the column gives me different values. That is already very good, but the icing on the cake would be if the column header would change with the value that was chosen in the slicer.
Nondeju 😉. Thats a pitty. As said that would be the icing. Then users would see the Actuals and the name of the Budget version they chose from the slicer next to each other in the visual. Kinda expected that to be an option if you can see what great features PowerBI already has. Maybe in future releases 😁
Hi Bas, I have a matrix visual with 3 fields in the Rows section, say RowField1, RowField2, and RowField3. By default, the visual only displays the first field name as the "column label" for the row headers. I want to show the user what fields are in the row hierarchy when they expand. Is there a way to put into a measure the concatenated row field names so that I can use it as part of the visual's title? The idea is to make it dynamic so that if the user personalizes the visual and added a 4th row field, it will show in the concatenated field list in the title. Something like RowField1/RowField2/RowField3, then RowField1/RowField2/RowField3/RowField4 when a 4th field is added by the user. Thanks in advance!
Hi Karanjeet, yes - that would work in the same way .. so you would have something like this Title = "Range: "&MIN(financials[Price]) & " - " & MAX(financials[Price] )
@@HowtoPowerBI thanks for your reply and your content is very helpful, but my need is different... Suppose we have a month column and we want heading on selecting jan, feb, mar and dec like that : "Jan-Mar, Dec".... Could you please help me with this. Im not an expert in powerbi.. I found a video but unable to understand it properly.
logic would be the same. you need a measure that returns the concatenated list of months. You can then use that measure for conditionally formatting the title. You can use the quick measure to create the concatenated list so that you dont have to bother with dax :)
Is it possible to have the dynamic title show when a slide slicer (periodic) is used for year, this is the slicer to show the earliest and latest year. Instead of having a one year select slicer we use a slide that shows Minimum and maximum date
Yes, just adjust the measure that you use ... something like this: Chart Title - Years Selected = VAR MinYear = YEAR(MIN(dimDate[Date])) VAR MaxYear = YEAR(MAX(dimDate[Date])) VAR Prefix = "Sales for " VAR Result = IF( MinYearMaxYear, Prefix & MinYear & " to " & MaxYear, Prefix & MinYear ) RETURN Result
This is just what I need but unfortunately when PBI is connected to tabular model I can't do this.. I can create the "Chart Title"-measure but I can't choose it from "Based on field"-dropdown list
I have a column in my table name reason for leaving and based on that I want to add another column which is Volunteer attrition or Involunteer attrition i.e. if reason for leaving for an employee appears as Retired then the new column should show Involunteer attrition whereas for resignation it should show as volunteer attrition. Can you help me add this column please? Also how can I calculate the percentage of volunteer and involunteer attrition based on the total attrition for the whole year which is 1157.
Hi Riaz, probably the easiest to add a conditional column in power query. Regarding the % calculation you could add measures like this: Attrition Voluntarily = CALCULATE(SUM(Data[Attrition]), Data[Reason]="Voluntarily") Attrition % of Total = DIVIDE([Attrition Voluntarily], ALLSELECTED(Data[Reason]))
@@HowtoPowerBI thanks for your reply can you please also confirm that if I have a free power bi license then will I be able to create workspace and share reports with people within my organizations or will I require a Power BI Pro or Premium license also will the users whom I share report with be able to view edit and share report with other people using free license?
@@riazuddin7493 no you as well as the receiver would need a pro license .. you could also go for a premium license, then you pay for capacity and pro license for people that need to publish
@@HowtoPowerBI Hi thanks for your reply. Can you also please let me know if visual obtained through Q&A account for filters or they will show results from the whole dataset ignoring all filters. I tried but the filters are not working
thak you sir, please can you explain an a video how we use the dynamic segmentation to do an aged balance ( for customer) by defining rules for example (Last 30 days; Between 31 and 60 days;Between 61 and 120 days;Between 121 and 180 days....) thanks in advance
Perfect, thanks a lot! One additional idea from my side. In case you would like to combine year and month automatically, e.g. to avoid manual adjustments in your page headers, you can also include such measures into the text fields. Here is an example, including the "what if you'd select all month" solution from your slicer.
IF([FilterMonthNumber]="",
COMBINEVALUES(
" ", ""&RIGHT([FilterYear],2), "."&"Full Year"
),
COMBINEVALES(
" ", ""&RIGHT([FilterYear],2), "."&[FilterMonthNumber]
)
)
Hey, can i know how it works for date slicer with dates in between? Can we get selected dates range in the title??
This channel is a gold mine! I will keep digging ;)
Thanks for this tip.just one more point to be more dynamic,instead of hard coding the 2020 as current year,it can be applied my max year right?
Yes that would work :)
excellent video, i have added dynamic natural language like titles to many of my reports from your guidance. Quick question, what should I do to show " Bluetooth Headphones, Cellphones & Computers". I'm trying to have it use natural language verbiage whereby if more than 1 is selected, there is an & between the last and second last selection and a comma in all other instances before. anyhelp here would be much appreciated!
Hello Bas, great video. I am wondering if there is a way to get in your measure, multiple selected values from a year slicer such as: whenever the year are consecutive, then the minyear-maxyear would be returned for that interval, and concatenated with other selected years that are outside of the interval.
For example, imagine i have the range of years 2010-2023 in my year slicer.
consider that the user has selected the years, 2012, 2013, 2014, 2017, 2019, 2020, 2021 then the measure should return 2012-2014, 2017, 2019-2021
Thanks
Awesome, will this work for entire dashoboard tilte where we use Text box?
This is the best channel when I face hurdle in design dashboard. The BEST channel
Thanks for the clarity on this topic. It seems we can't use the new field parameter SELECTEDVALUE to dynamically change the corresponding visual title. Is there a way to make this work?
Did you find an answer I have been looking forever
I was literally looking at some dynamic titles videos and you just posted this awesome tutorial!!
Thanks a lot mate!! 💯
I saw you looking for the topic, so quickly created on for you 😉😋 thanks for watching Yeriel!
@@HowtoPowerBI That's what I call a Good Listener 👂👏.
Keep the great videos coming mate!!
@HowtoPowerBI - Thanks for the wonderful video. I'm using your "SWITCH between PERCENTAGE and ABSOLUTE VALUES" conditional group measure here to show Sales # when i select absolute value and Sales% when i select percentage value. Chart title is changing as Sales# if i select absolute value, but if i select percentage value chart is not loading the data with error "This type of text cannot be converted as numeric. Can you please help.
Hello Bas, i am trying to apply the instructions you gave in the video on a slicer that was created from Field Parameters, however i am getting the following error: MdxScript(Model) (48,14) Column['column_name'] is part of composite key, but not all columns of the composite key are included in the expression or its dependent expression. Does your method works on slicers created by Modeling-> Modeling -> Fields
When you reference the first column of a fields parameter inside of a selectvalue function you get that error… instead use MAX() or alternatively use selectedvalue on another column (can be calculated column)
This was awesome! Perfect use case for me is to build KPI with this about working hours low, in limits, too much
Another nice one . Thanks . I have an issue. ISFILTERED did not work. It still shows all values for title.
same issue.
Hi Bas,
I want to know if it's possible to make the dynamic title show between two selected years say, 1990 and 2000?
Great video! Do you have any tips for how you would handle displaying the selection when the selected value in the filter is "greater than 30" for example?
build in logic like -> IF ( [measure] > 30, "text" )
This guy is better than NETFLIX!!!
Another amazing video!
keep up the good work Bas!
Hahah hillarious! Thanks 🙏
nice one! Do you plan something about improving efficiency of the reports?
defiinitely on my list 🙂
Another superb video from Bas. Was not aware of that COMBINEVALUES technique at minute 11. If you get a chance, would you mind attaching the PBIX file to your comments section. Thank you!
Thanks CJ! I've added the download link to the description section. 🙂
Hi Bas, many thanks, it's a pleasure following your tips.
Is there a way to use a selected value from a graph instead of the value from the slicer?
Again, thanks and best regards
How we can add colour in this dynamic title measure if it is already used in another measure but i want to add colour only in this quick measure?
Hey Bas, I have a question for you. Have you ever managed to get this working successfully with a Hierarchy in the slicer? When I select the lowest level of the slicer (in my case store names) the measure works. However, if I select a level higher (region - which automatically selects the stores) the filter doesn't work. Any idea how I may be able to fix this? Thanks for the video, was exactly what I was looking for a couple months ago!
Solved. I added multiple ISFILTERED('table'[column]) representing each level of the hierarchy with an or "||."
Here is the top part of the measure for anyone wants to see it.
VAR __DISTINCT_VALUES_COUNT = DISTINCTCOUNT('Date'[Fiscal W#])
VAR __MAX_VALUES_TO_SHOW = 13
RETURN
IF(
ISFILTERED('Date'[Fiscal Year Name])
||
ISFILTERED('Date'[Fiscal QTR Short Name])
||
ISFILTERED('Date'[Fiscal Period Short Name])
||
ISFILTERED('Date'[Fiscal W#]),
IF(
__DISTINCT_VALUES_COUNT > __MAX_VALUES_TO_SHOW,
let me know how can we create dynamic titles for measure switches as it creates dependency error if follow the same thing.
Slicer is the key tool of Power bi 👍
Great learning.. thank you.! 😁👌👌
thanks for watching Raj! can't live without them 😉
How is a graph like this created?
Thanks a lot for your videos. The best channel for Power BI.
Thanks Sumbat, glad to hear you think so!
How does this work for a heirarchy level?
If you choose the 2 lines symbol to drill down a whole hierarchy level, the title changes automatically.
But if you customize the automatic title, it wont work anymore.
Which DAX measure can I use to change the title, so that it shows the selected field name of that hierarchy level?
I need to include an explanatory note in the reports. Does anyone have any ideas on how to do that? Each graph needs to have an analysis note, along with the option to show and hide this note.
I just used the switch function and it worked like a charm for me.
Hi BAs, really enjoy your videos and they have enhanced my reports immensely. Is it possible to highlight or colour specific words or numbers in a dynamic title?
what if all values selected or all are not selected?
Hi
First of all thank you very much for this amazing content.
Please keep doing the good work man.
Just one issue , i am not able to implement the " All Skills" concept< please help !!!!!!!
what if I want to display the previous year on a card with the title "Sales for 2018" if I selected 2019 from the year slicer? how do i get to change the header to calculate "-1"?
Great Video! So well explained and covering all problems that could appear in the process. Keep up the good work :) Thanks!!!
Very very very nice. Great explanation, just the right speed, and of course, super content! Thanks a lot
Thank you so much Erica for watching!! 😀
If I don't select anything in dynamic filter it should give me slicer header.. How to achieve... I'm getting dynamic filter by category but in select all I'm facing issues
Amazing! it coveres eveything needed for dynamic Title :)Thanks!!
I used the concatenated version of the measure to be able to select multiple items. I am facing an issue where if I have all multiple selected, the text box reflects that value, but when I deselect an item, it does not remove the name from the textbox.
Example:
Slicer has the following selections:
Apples
Bananas
Watermelon
Text box shows: "Apples, Bananas, Watermelon"
Slicer changes selection to be:
Apples
Watermelon
Text box shows: "Apples, Bananas, Watermelon"
Any idea how to fix this? Thanks
Nevermind. I determined my issue. I created a column rather than a measure.
Hi Bas, many thanks, it's a pleasure following your tips. you are awesome
How to add Today's date in power bi title..like i.e. Data Refreshed on "Todays Date"..kindly suggest your video
You helped me a lot! I could find what I wanted. Keep going! Great video!
Hi ,
Can you create a table as per slicer selection .. consider I'd column in a rows like 1,2,3,4. And a slicer using this I'd column.. now if I select I'd from slicer 3 then a table will be created which has all data with I'd 3. Please let me know if requirement is not clear
You can only pass the selected value of a slicer to measures - not tables or calculated columns.
@@HowtoPowerBI so no work around for that.. thank you for replying
Would love to know how to include only the values excluded from a slicer… this is awesome btw
Hey Bas, really explained well. Just want to solve one problem, for YTD values of Year/Quarter/Months selections I want to keep showing only latest Year/Quarter/Months in dynamic Title though multiple selections applied, Please help!!
excellent explantion,Thank you for the video
Glad it was helpful! 😎👊
Superb. Can something like this be done on the header of measures in a matrix??
I have a measure that is linked to a slicer. Options are LBE1 to LBE4. Depending on the choice the column gives me different values.
That is already very good, but the icing on the cake would be if the column header would change with the value that was chosen in the slicer.
nice idea! don't think that is possible though 🙄😕
Nondeju 😉.
Thats a pitty. As said that would be the icing.
Then users would see the Actuals and the name of the Budget version they chose from the slicer next to each other in the visual.
Kinda expected that to be an option if you can see what great features PowerBI already has.
Maybe in future releases 😁
Thanks a millon !! the idea you brings everytime it helps a lot.
Hi Bas,
I have a matrix visual with 3 fields in the Rows section, say RowField1, RowField2, and RowField3. By default, the visual only displays the first field name as the "column label" for the row headers. I want to show the user what fields are in the row hierarchy when they expand. Is there a way to put into a measure the concatenated row field names so that I can use it as part of the visual's title? The idea is to make it dynamic so that if the user personalizes the visual and added a 4th row field, it will show in the concatenated field list in the title. Something like RowField1/RowField2/RowField3, then RowField1/RowField2/RowField3/RowField4 when a 4th field is added by the user.
Thanks in advance!
Thanks Baz.. How to add title in two lines. Sub category in first line and year in second line. (Breaking the title in two lines)
Double thumbs up.👍👍
Awesome, But how we can change column name dynamically based on filter selection
Is there any way to give range of values in dynamic title?
Hi Karanjeet, yes - that would work in the same way .. so you would have something like this Title = "Range: "&MIN(financials[Price]) & " - " & MAX(financials[Price] )
@@HowtoPowerBI thanks for your reply and your content is very helpful, but my need is different... Suppose we have a month column and we want heading on selecting jan, feb, mar and dec like that : "Jan-Mar, Dec".... Could you please help me with this. Im not an expert in powerbi.. I found a video but unable to understand it properly.
logic would be the same. you need a measure that returns the concatenated list of months. You can then use that measure for conditionally formatting the title. You can use the quick measure to create the concatenated list so that you dont have to bother with dax :)
@@HowtoPowerBI will it be dynamic for any range of months?
@@karanjeetwaraich3210 yes
Bas is always on point ,this guy is genius 👏 👌 ..
Is it possible to have the dynamic title show when a slide slicer (periodic) is used for year, this is the slicer to show the earliest and latest year. Instead of having a one year select slicer we use a slide that shows Minimum and maximum date
Yes, just adjust the measure that you use ... something like this:
Chart Title - Years Selected =
VAR MinYear = YEAR(MIN(dimDate[Date]))
VAR MaxYear = YEAR(MAX(dimDate[Date]))
VAR Prefix = "Sales for "
VAR Result =
IF(
MinYearMaxYear,
Prefix & MinYear & " to " & MaxYear,
Prefix & MinYear
)
RETURN
Result
@@HowtoPowerBI Once again, you made it look easy. Big thumbs up
@@HowtoPowerBI thank you!!! Now I just need to work out how to show the exact dates instead of a year range
Which I've done... for anyone else I replaced the YEAR with DATEVALUE
This is just what I need but unfortunately when PBI is connected to tabular model I can't do this.. I can create the "Chart Title"-measure but I can't choose it from "Based on field"-dropdown list
I have a column in my table name reason for leaving and based on that I want to add another column which is Volunteer attrition or Involunteer attrition i.e. if reason for leaving for an employee appears as Retired then the new column should show Involunteer attrition whereas for resignation it should show as volunteer attrition. Can you help me add this column please? Also how can I calculate the percentage of volunteer and involunteer attrition based on the total attrition for the whole year which is 1157.
Hi Riaz, probably the easiest to add a conditional column in power query. Regarding the % calculation you could add measures like this:
Attrition Voluntarily = CALCULATE(SUM(Data[Attrition]), Data[Reason]="Voluntarily")
Attrition % of Total = DIVIDE([Attrition Voluntarily], ALLSELECTED(Data[Reason]))
@@HowtoPowerBI thanks for your reply can you please also confirm that if I have a free power bi license then will I be able to create workspace and share reports with people within my organizations or will I require a Power BI Pro or Premium license also will the users whom I share report with be able to view edit and share report with other people using free license?
@@riazuddin7493 no you as well as the receiver would need a pro license .. you could also go for a premium license, then you pay for capacity and pro license for people that need to publish
@@HowtoPowerBI Hi thanks for your reply. Can you also please let me know if visual obtained through Q&A account for filters or they will show results from the whole dataset ignoring all filters. I tried but the filters are not working
@@riazuddin7493 report filters also apply to the q&a visual
I'm offended by those two dislikes at the top.
hahaha thx Gambu!!!! 😄
How to show different names or title instead of customer names in visual
You saved my life bro
Saving lifes with Power BI is what i do 😄😂 im happy it helped! Thx for watching!!!
If user selects 2020, 2021, and 2022, how do you make it display "2020-2022"
Brilliant, many thanks for your video. Helped me in my job.
Great to hear! thx for watching Robert
Thank you! Very helpful!
Glad it was helpful Yuki!
Great video!
thanks for this video,can I get this pbix file?
thanks Sathish! I have added the download link in the description 🙂
@@HowtoPowerBICould you please cross check with your download link thanks!
thak you sir, please can you explain an a video how we use the dynamic segmentation to do an aged balance ( for customer) by defining rules for example (Last 30 days; Between 31 and 60 days;Between 61 and 120 days;Between 121 and 180 days....) thanks in advance
Here you go
ua-cam.com/video/VrVqseNAVH4/v-deo.html
@@HowtoPowerBI lot of thanks
Great video!!😃
Great video!
Thanks Kenneth!
Awesome information, thanks!
Glad it was helpful! thx for watching!!! 😀
many thanks for this !
Great video
Glad you enjoyed it, thanks!
Great video, thanks for the help
Glad it helped thx Miguel! 😊😀
That is awesome work and great explanation
thx Sharoon! 😃
Just great!
Thanks a lot ☺️
Very useful ........
Thx! Happy to hear this
very gooooood!!!
thanks Pedro!
thank you
Yes, Yes, Yes!!!
Wow!