Thanks Patrick for the videos. I would like to know how do you select records from a table within a date-range, where start date and end date are in two separate measures?
Patrick-- great use of SELECTEDVALUE with drillthrough filters! I use a Card visualization because I get greater control of the formatting: background & text color, and no need to have a title for my title. However, neither a card nor a table scale well with phone layout... horizontal scrolling is a bummer.
That's really useful, thanks. I've been using the concatenate list of values quick measure for displaying multiple selected items in the same field. Your demo is perfect for single values from multiple fields. Thanks again
Just a quick note about SelectedValue you can set an alternate value so the ISBLANK isnt necessary. eg. Selected Project = var projetid = SELECTEDVALUE('Project'[Proj ID], "All") return "Project Id: " & projetid I have listed "All" as the alternate when [Proj ID] does not exist.
What we still need in Power BI, is being able to execute DAX variables, similar to dynamic T-SQL, thereby creating extremely dynamic DAX code and programmatic opportunities. If you agree, please up-vote the idea here: ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/40686367-dynamic-dax-executable-variables-treat-variab
OMG! now this is simply F... abulous A-MA-ZING! how easy and how strong explanation. I remember I had a requirement to do something like this but as the data source was SSAS we were trying to implement some logic directly in the cube, but the alternative from PBI is just simple and effective. Good to have this in the arsenal. Thanks!
Hi, Is it possible to name the table headers based on some filtering. For example: My report contains data for 3 different periods - month, day, week. And I want when I change the period the correspondent column headers to become - lets say DayValue, WeekValue, MonthValue
very cool! thanks! When I did something similar, I had a lot of formatted info to show including email addresses and hyperlinks and I just used the html viewer and had a column in my data that concatenated my html. Then the drillthrough filter limited the display to the single item.
I've been using measures to do this so far. The DAX you just demonstrated is cleaner and I will adopt it going forward. BUT!!!, it doesn't address the main issue. The header is displayed in a separate visual (in this case a table). That doesn't translate to a clean professional look on mobile reports. Need the capability to use measures in titles and axes labels for all / most visuals. Even though your demo works, I view this as a partial solution / work around.
Hi Patrick, love your videos. Just wondering how you do this if you are connecting to a SSAS 2012 Tabular model, which doesn't have SELECTEDVALUE. Thanks
Hi. Thank you this is great. What about if multiple values are selected. I have added the selected variable to a text box and it only returns the first value. What should I do?
Hi, I want to change the label only in case of drill-down, but it's changing if I select any value on the bar chart? In my case instead of setting the dax on the title of the graph, I used it on a text box for better and field. Dax which I am using : A_TITLE_BAR_1_3 = var selPosition = SELECTEDVALUE ( application_overview[position_type]) return IF(ISBLANK(selPosition),"Average Time To Resolution By Position Type(Days)","" & "Average Days Per Workflow State ("&selPosition&")")
Hi, Please let me know if there is any way to edit/choose Query or List parameters in Power BI service (just like you had done here in PBI desktop using Edit Parameters option. Like here u can choose the value from tcountries parameter in desktop but is it possible to choose that parameter values in Power bi service as well?
This is awesome, thanks Patrick. I have noticed, though, when trying to filter multiple but not ALL of a Name field, it comes up with the "All Names" instead of listing the names... Any thoughts?
Hey Patrick, pretty new to Power BI, I wanted to show and hide columns based on slicer selection, columns are like Effective_Date, Purchase_Date, Quantity, Order_Number. How can I achieve this. I have seen so many videos of show/hide on measure columns not on dimension or attribute or date columns. Your video on this will be much appreciated
Hi Patrick, can you teach us how to have editable commentary box/long text that responds with dynamic data on my PBI report/dashboard? I'd like to input and delete words on my dashboard (that follow the change of my dynamic data. It's such a big demand but no one has the demo.
How could you do this for a column? I know how to do it in SSRS but Power BI is a new venture and am wanting to replicate what we have with some tweaks. The columns need to say the date plus so many dates depending on todays date
I got a question, if I have a bar chart, but I want to display two associated values, for example i have column of percentage and a colum of complete task, i want to display them as percentage in the bar, but when i click or put the mouse uppon the bar, I want it to display the 2 values. like 2 labels, one: the percentage, the second: the data with has the colum of the completition of the task-. How can i do that?
3:54 I have an owlful vertical line there of grey color. The vertical line where you click at 3:54 to expand column in your case is white or invisible. How to make it so?
You can modify the color settings of a table or matrix when you select the paintbrush section in Power BI Desktop. There are pre-designed looks, of you can fine tune it.
For those of you getting this error: "Function 'COUNTROWS' is not supported in this context in DirectQuery mode" Please make sure you enable Unrestricted Measures in DirectQuery mode: community.powerbi.com/t5/Desktop/Filter-is-not-working-in-Direct-query-mode/td-p/133492
Great video! I am trying to create the same thing but with comments. I have three countries and I would like to have comments specific for each country but also a comment when no country is selected (general comment for the nordics), do you know if this is possible?
Thank you for the cool videos, let me ask you something is there any special software for your mouse pointer, would be nice to use it for my students, I' currently using ZoomIt, I think that you are using a much better software can you recommend me which one?, Thanks!!
I'm trying to figure out how to do the same thing but by extracting the month and year from a selected date range when the range selected is within the same month/year. For example, Jan 1, 2018 to Jan 31, 2018 I want the label to show January 2018. Is this possible?
Patrick, We need a SelectedMeasureDescriptionString function so we can display the description property from SSAS to people who might not understand the business logic. I'm thinking custom tooltip page Please help, Eric
Hi Patrick, awesome shirt. So I've been using a card visual for this, with stuff like Title = "Project:" and Field = First Project since only 1 project is selected when you do a drillthrough. What's the added benefit of using DAX for it instead?
Grt !! But we don't need any measures (DAX) like Selected Color and Selected Country ... we can directly use the drill through functionality provided by PBI as it catch the event like SELECTED VALUE by default .. Use the color and country as drill though parameters in the details page and that's it DONE !!! ..Let me know if I miss anything here ...
If I Want to change all the tilte of the chart (even the colum name) for their good traduction (english to spanish, german, ...) and make the colum name change, how can I do it?
Appreciate the comment. I believe you are referring to the DAX editor within Power BI Desktop and the syntax highlighting it has. We don't really have control over that, unfortunately. I believe DAX Studio does the same, although I'd have to look if you can control the colors similarly to Visual Studio.
Cool, Thanks - Is it possible to rename a measure based on Source Name (Eg, I have created 200 measures,50 per year for 4 years like Revenue, Cost, GM etc), when I replace 2018 data with 2019 Data, can my measure name change from 2018 Revenue to 2019 Revenue
i have found the issue working with the direct query please find the query and error. please comment if anyone get's the same issue. DAX : Country = var selectedcountry = SELECTEDVALUE(DimSalesTerritory[SalesTerritoryCountry]) return "Country: "& IF(ISBLANK(selectedcountry), " OverAllTerritory", selectedcountry) Error : Function 'COUNTROWS' is not supported in this context in DirectQuery mode.
Increíble de verdad, tengo un conocimiento de como usar Power BI, pero me gustaría obtener una certificación de Microsoft, ¿ como podría obtenerla?, muchas gracias de antemano!
Hi Paula, i hope you have figured this one out by now, but if not, here is how i achieved what you are asking: Selected Stuff = IF(ISFILTERED(TableName[ColumnName]), CONCATENATEX(values(TableName[ColumnName]),TableName[ColumnName], ","), "No Filter")
Hi Mr. Patrick, How are you? I'm from Brazil and like much your movies. Tks so much for teaching. Please, help me? I need to compare the same criterial of January and February However I have three criteria at February and January two, I want it to appear just compared to january, that is two. please, you understand? If yes, can you're me help? TKS. Janeilson de Sousa
Hi Guys, I'm using this to select the current date selected: Selected Date = VAR SelectedDate = SELECTEDVALUE('Date Init'[Selected]) RETURN IF(ISBLANK(SelectedDate) ,0 ,SelectedDate) Then I want to compare this selected value to another table to put a flag: Flag = IF ('Date'[Selected]=[Selected Date] ,1,0) Someone know why the second formula is not working, and how I can solve this?
Hi Niranjan, i hope you have figured this one out by now, but if not, here is how i achieved what you are asking: Selected Stuff = IF(ISFILTERED(TableName[ColumnName]), CONCATENATEX(values(TableName[ColumnName]),TableName[ColumnName], ","), "No Filter")
![[SFM] Risky Recital](http://i.ytimg.com/vi/RzNkY1_Nk3o/mqdefault.jpg)
omg you're videos are God send and I love your energy and the way you present! Thank you!
Thanks Patrick for the videos. I would like to know how do you select records from a table within a date-range, where start date and end date are in two separate measures?
Patrick-- great use of SELECTEDVALUE with drillthrough filters! I use a Card visualization because I get greater control of the formatting: background & text color, and no need to have a title for my title. However, neither a card nor a table scale well with phone layout... horizontal scrolling is a bummer.
That's really useful, thanks. I've been using the concatenate list of values quick measure for displaying multiple selected items in the same field. Your demo is perfect for single values from multiple fields. Thanks again
Patrick! You are the man, thanks a bunch for this. The entire video was easy to follow and I appreciate the work put into it.
Great! just one point the if condition is extra you could just use the AlternateResult and set it to All Colors
Love the selected value dax expression.
Sweet video, clean & to the point
Just a quick note about SelectedValue you can set an alternate value so the ISBLANK isnt necessary.
eg. Selected Project = var projetid = SELECTEDVALUE('Project'[Proj ID], "All") return "Project Id: " & projetid
I have listed "All" as the alternate when [Proj ID] does not exist.
What we still need in Power BI, is being able to execute DAX variables, similar to dynamic T-SQL, thereby creating extremely dynamic DAX code and programmatic opportunities.
If you agree, please up-vote the idea here: ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/40686367-dynamic-dax-executable-variables-treat-variab
The test on isblank is not required. SelectedValue has 2 parameters. Right usage is : SelectedValue (Table [column], "default value")
won't the IF statement always evaluate to TRUE if the is black function is removed. and the result will always be "Color is : All colors" ?
OMG! now this is simply F... abulous A-MA-ZING! how easy and how strong explanation. I remember I had a requirement to do something like this but as the data source was SSAS we were trying to implement some logic directly in the cube, but the alternative from PBI is just simple and effective. Good to have this in the arsenal. Thanks!
Great post Patrick! As always!!!
Using SELECTEDVALUE for this now, BUT definitely have to start using variables, like that a lot!
This is great thanks so much!
Is it possible to display more than one selected value when drilling through?
Exactly what I was looking for
Thankyou Patrick for all your videos. Please let me know if there is any way to detect device (Mobile/Desktop) deatils from current Power BI report.
Hi, Is it possible to name the table headers based on some filtering. For example: My report contains data for 3 different periods - month, day, week. And I want when I change the period the correspondent column headers to become - lets say DayValue, WeekValue, MonthValue
very cool! thanks! When I did something similar, I had a lot of formatted info to show including email addresses and hyperlinks and I just used the html viewer and had a column in my data that concatenated my html. Then the drillthrough filter limited the display to the single item.
very cool! thanks for watching!
Hi. That was great, very informative. How would you go about showing if you had selected multiple colours?
yes same issue. how can it be the example only caters for one value
Can you make the chart title dynamically change based on the selection within the same chart ??
I've been using measures to do this so far. The DAX you just demonstrated is cleaner and I will adopt it going forward. BUT!!!, it doesn't address the main issue. The header is displayed in a separate visual (in this case a table). That doesn't translate to a clean professional look on mobile reports. Need the capability to use measures in titles and axes labels for all / most visuals. Even though your demo works, I view this as a partial solution / work around.
Hi Patrick, love your videos. Just wondering how you do this if you are connecting to a SSAS 2012 Tabular model, which doesn't have SELECTEDVALUE. Thanks
Hi Patrick, I have been using "FIRSTNONBLANK" function to do the same trick.
Awesome! Yes that is another way to do it!
Thanks for the video. Helped me with a requirement
Hi. Thank you this is great. What about if multiple values are selected. I have added the selected variable to a text box and it only returns the first value. What should I do?
Hi, I want to change the label only in case of drill-down, but it's changing if I select any value on the bar chart?
In my case instead of setting the dax on the title of the graph, I used it on a text box for better and field.
Dax which I am using : A_TITLE_BAR_1_3 =
var selPosition = SELECTEDVALUE ( application_overview[position_type])
return
IF(ISBLANK(selPosition),"Average Time To Resolution By Position Type(Days)","" &
"Average Days Per Workflow State ("&selPosition&")")
Hi,
Please let me know if there is any way to edit/choose Query or List parameters in Power BI service (just like you had done here in PBI desktop using Edit Parameters option.
Like here u can choose the value from tcountries parameter in desktop but is it possible to choose that parameter values in Power bi service as well?
Hi Patrick, do you have an easy way to show the values of multiple selected items in the same visual?
I was going to ask an exact same question
Use concatenatex(
@@MishaBEZEDE in the same expression?
@@AJM2183 as a separate measure
@@MishkyGammy I found Patrick's other video where he addresses the issue. All good for me now!
This is awesome, thanks Patrick. I have noticed, though, when trying to filter multiple but not ALL of a Name field, it comes up with the "All Names" instead of listing the names... Any thoughts?
Hey Patrick, pretty new to Power BI, I wanted to show and hide columns based on slicer selection, columns are like Effective_Date, Purchase_Date, Quantity, Order_Number. How can I achieve this. I have seen so many videos of show/hide on measure columns not on dimension or attribute or date columns. Your video on this will be much appreciated
Can we dynamically change the "Category Label" for any visual like card or multi row card or any work around
Thanks so much for sharing this. Super helpful.
Glad it helped you! Thanks for watching! 👊
Thanks Patrick! This is just what I was looking for! Your videos are great!
Hi Patrick, can you teach us how to have editable commentary box/long text that responds with dynamic data on my PBI report/dashboard?
I'd like to input and delete words on my dashboard (that follow the change of my dynamic data. It's such a big demand but no one has the demo.
Thanks for the great videos. Do you have any videos on using this principle but dynamically changing an image?
How could you do this for a column? I know how to do it in SSRS but Power BI is a new venture and am wanting to replicate what we have with some tweaks. The columns need to say the date plus so many dates depending on todays date
Cool, but I also hoped it would be possible to use these variables in the column titles. Would have made this so much better!
Will the Selected value method work if you select multiple colors, for example blue and red or multiple countries?
I got a question, if I have a bar chart, but I want to display two associated values, for example i have column of percentage and a colum of complete task, i want to display them as percentage in the bar, but when i click or put the mouse uppon the bar, I want it to display the 2 values. like 2 labels, one: the percentage, the second: the data with has the colum of the completition of the task-.
How can i do that?
3:54 I have an owlful vertical line there of grey color. The vertical line where you click at 3:54 to expand column in your case is white or invisible. How to make it so?
You can modify the color settings of a table or matrix when you select the paintbrush section in Power BI Desktop. There are pre-designed looks, of you can fine tune it.
For those of you getting this error: "Function 'COUNTROWS' is not supported in this context in DirectQuery mode"
Please make sure you enable Unrestricted Measures in DirectQuery mode:
community.powerbi.com/t5/Desktop/Filter-is-not-working-in-Direct-query-mode/td-p/133492
Great video! I am trying to create the same thing but with comments. I have three countries and I would like to have comments specific for each country but also a comment when no country is selected (general comment for the nordics), do you know if this is possible?
Thank you for the cool videos, let me ask you something is there any special software for your mouse pointer, would be nice to use it for my students, I' currently using ZoomIt, I think that you are using a much better software can you recommend me which one?, Thanks!!
awesome videos.. thanks man.. hugs from brasil
"What a silly bot you are!" HAHA. Great use of DAX!! Thanks for the videos as always!
Hi Patrick, Is there a way to wordwrap the text in legends in the visualization?
I'm trying to figure out how to do the same thing but by extracting the month and year from a selected date range when the range selected is within the same month/year. For example, Jan 1, 2018 to Jan 31, 2018 I want the label to show January 2018. Is this possible?
Thanks for the video Patrick. But what if I have filter with multiple selection? How to show a legend "Multiple Selection" in my label?
Have a look at the tooltip quick measure. It shows an example of this. Basically an IF and then concatenate a string after it.
Can I dynamically use a measure on the title text of the visual??
Thanks for the video! We need this same functionality for multiple selections- any suggestions?
Found the answer here: ruclips.net/video/SjSKCZtiNlI/видео.html
Create a Quick Measure- Concatenated List of Values
Thanks Guy in a Cube!!
Patrick,
We need a SelectedMeasureDescriptionString function so we can display the description property from SSAS to people who might not understand the business logic. I'm thinking custom tooltip page
Please help,
Eric
Hi Patrick, awesome shirt. So I've been using a card visual for this, with stuff like Title = "Project:" and Field = First Project since only 1 project is selected when you do a drillthrough. What's the added benefit of using DAX for it instead?
Bump
Grt !! But we don't need any measures (DAX) like Selected Color and Selected Country ... we can directly use the drill through functionality provided by PBI as it catch the event like SELECTED VALUE by default .. Use the color and country as drill though parameters in the details page and that's it DONE !!! ..Let me know if I miss anything here ...
hi! what's the difference between selectedvalue and isfiltered?
Thank you Patrick, very helpful!
Glad it helped! Thanks for watching 👊
Hi Mate, which laptop do you use? Thanks and I love your videos btw
If I Want to change all the tilte of the chart (even the colum name) for their good traduction (english to spanish, german, ...) and make the colum name change, how can I do it?
How can I do this if I'm using SSAS multidimensional model?
Do we have to have bright cyan text on a bright white background in your coding, please? It's near impossible to read due to lack of contrast!
Appreciate the comment. I believe you are referring to the DAX editor within Power BI Desktop and the syntax highlighting it has. We don't really have control over that, unfortunately. I believe DAX Studio does the same, although I'd have to look if you can control the colors similarly to Visual Studio.
Cool, Thanks - Is it possible to rename a measure based on Source Name (Eg, I have created 200 measures,50 per year for 4 years like Revenue, Cost, GM etc), when I replace 2018 data with 2019 Data, can my measure name change from 2018 Revenue to 2019 Revenue
Wondering if you ever got a solution to this?
Cool video, but where can I get that t-shirt?
i have found the issue working with the direct query please find the query and error. please comment if anyone get's the same issue.
DAX : Country = var selectedcountry = SELECTEDVALUE(DimSalesTerritory[SalesTerritoryCountry]) return "Country: "& IF(ISBLANK(selectedcountry), " OverAllTerritory", selectedcountry)
Error : Function 'COUNTROWS' is not supported in this context in DirectQuery mode.
Simple and very useful!! Tks
How do I change my data labels from % to number?
Awesome!! Thank you!!👍👍
Excellent
This is great! but... How to you do it using a card?
Forget it, i just changed the visual to a card.
Thanks for the video.
Cool feature, very helpful. Ths Bro!
Thanks for watching Praveen!
Increíble de verdad, tengo un conocimiento de como usar Power BI, pero me gustaría obtener una certificación de Microsoft, ¿ como podría obtenerla?, muchas gracias de antemano!
Jose Berrios puedes tomar el siguiente examen: www.microsoft.com/en-us/learning/exam-70-778.aspx
Jonathan Tovar R muchas gracias.
How to display a word equivalent to a range number?
Hello, if I select two values it shows a error. How can I do a measure that allow to show more than one value selected?
Hi Paula, i hope you have figured this one out by now, but if not, here is how i achieved what you are asking:
Selected Stuff = IF(ISFILTERED(TableName[ColumnName]), CONCATENATEX(values(TableName[ColumnName]),TableName[ColumnName], ","), "No Filter")
Hi Mr. Patrick, How are you?
I'm from Brazil and like much your movies.
Tks so much for teaching.
Please, help me?
I need to compare the same criterial of January and February However I have three criteria at February and January two, I want it to appear just compared to january, that is two.
please, you understand?
If yes, can you're me help?
TKS.
Janeilson de Sousa
Hi Guys, I'm using this to select the current date selected:
Selected Date =
VAR SelectedDate = SELECTEDVALUE('Date Init'[Selected])
RETURN
IF(ISBLANK(SelectedDate)
,0 ,SelectedDate)
Then I want to compare this selected value to another table to put a flag:
Flag =
IF ('Date'[Selected]=[Selected Date] ,1,0)
Someone know why the second formula is not working, and how I can solve this?
I should of watch this video last week. Took me for ever to find out that I had a filter selected and I knew my data was wrong.
At least you know now!
how to handle if you have more than 1 value (say from the slicer where i can select multiple values)
Hi Niranjan, i hope you have figured this one out by now, but if not, here is how i achieved what you are asking:
Selected Stuff = IF(ISFILTERED(TableName[ColumnName]), CONCATENATEX(values(TableName[ColumnName]),TableName[ColumnName], ","), "No Filter")
Thanks Jeremy, this is what I was looking for :)
Thank you!!! very useful!!!!!!!!!!!!! gracias!!!!
Nice work! Just a little too fast towards the end there...
Appreciate the feedback! Thanks for watching!👊
Nice.. it helped. :)
It doesn't handle multiple selections in the same dimension.
Just show the screen instead of zooming at you talking and back to the screen, we can hear you without seeing you.
God
Why u shouting every single time?? 😂😂😛
Stop switching the camera and stay in Power Bi it makes the video unwatchable.
Appreciate the feedback Ringo! Thanks for watching 👊
Is there a way to get labels in a visual to change dynamically, for example if I want to create a multilingual pie chart?
Hi Patrick, Is it possible to have dynamic column names on based on selected filter ? When we are using Table visuals.