I love these videos because, not having done this in about 8 months, it auto-triggers many DAX/PBI concepts in my memory--not only on the topic of the video.
Thank you Alberto, so amazed by all your valuable contributions. I have learned a lot from you, but at the same time, realized how little I know ;-) But i'll keep on watching you videos, to get a better grip on the more advanced DAX. So happy for your contribution!!
This concept makes way more sense after reading your book since you could technically use a COUNTROWS(VALUES('Product'[Class])) . I didn't even think that total was responsible in this case at 3:00 for returning multiple values and generating the error.
Thank you so much for this video, I have searched severally and I see more complex solutions. Thanks for sharing and I hope to see more to help my learning
Very useful. I got curious about this function because Brian Julius and Melissa de Korte use it to make use of offset columns in a customized date table to make time intelligence measures much easier to write and more consistent.
I’m a new student and I want to say that I watch the presentation on Microsoft really you are funny teacher and lovely makes things easy and meeting is a dream Sir 🤞🙏🙏
This is just another fantastic video, by examples and presentation format from Alberto Ferrari :) Makes me feel like taking a lesson from a professor :) Thanks a lot.
Your videos help me a lot. Thank you so much for sharing your knowledge. I eagerly look forward to more of your videos. Can you make a video on usages and examples for cross filter?
Thanks. One issue is that without product class in Rows of the matrix the measure does not return the value in my case. (Values requires a companion for comparison)
Yes, but it's usually easier if you save in in a variable first. Because it is a scalar, you have to write a predicate expression, like table[column] = _variableWithSelectedvalueResult
Thanks for the video but would like to give my feedback. Tried as per your video but the if statement did not work ie although the sale column is zero or empty, the selected value is still showing the data. Any reason for this?
Hi, Thanks for such informative videos. I am not able to get values in the calculated column by the selected value it returns blank data in the column for example I have a measure [Req Days] = SELECTEDVALUE('Days'[Days]) Then I am creating new column with this code 5D_OSA_Required_Qty = ('BI Primary'[L3M_Sales_Qty]/90)*[Req Days] but not getting any result. pls help
Good option, very useful function, thank you Alberto. I normally take the easy say, and I drop the column in values, then select "First value" which happens to be the only one. I guess that there will be any flaw under this option?
It works most of the time, but if there are multiple values you don't see a possible issue (SELECTEDVALUE may be better returning BLANK in the same condition).
Thank you very much for these types of videos, the best explanations. Also I have discovered the use of crossfilter in this situation. Question: I would have written this form: calculate( selectedvalue ('product category' [category]) , product ) There is some advantage to using crossfilter over just putting the entire product table as a filter Greetings!!
@@SQLBI I think the problem is that the SELECTEDVALUE () function is not evaluated before FILTER (). Explaining further: I'm trying to use the formula: filtered_data = FILTER (data, data [value]
You can use variables to change evaluation order, but it doesn't seem the case here - if you are using a disconnected table for Percentile it should have worked. There is probably something else in your model or formula.
@@SQLBI The table for Percentil is disconnected from table 'data' but it has a calculated column: Percentil_Value = VALUE(PERCENTILE.INC(data[value],(Percentis[Percentil]/100)))
Hi Alberto thank you for your great videos. I am trying to solve function with a Parent selected input, but I want to split this selection in children. Is their a method to do that?
I have an issue. I wanted to calculate the lowest value in a column for each year. Using the min implicit function I am able to get the minimum value. But when I put this on an animated bar chart, it displays all the minimum values of that particular year. Whereas, I wanted the lowest value per year to be displayed. Here is the code in DAX which I tried but the result is the same: Lowest per year = MINX (FILTER ( Dates, Dates[Year]), MIN('Score board'[Lowest Total.Score] ) )
It would be good if SELECTEDVALUE can handle with multiple selection. According to calculation condition, multiple selection might sum all selected ones or whatever calculation is it might be do the same for both selection.
Could you please help on the below Usecase: In table visual... Selected columns from dimension and fact table. There is a date column from fact table used in visual. Expected use case: in the table visual Need to pick only the latest record(max entry) for each year based on date column
ISEMPTY should be faster, ISBLANK requires evaluating [measure], which is certainly more expensive. However, actual difference depends on [measure] complexity, for a very simple measure the difference could be minimal.
why can't I define a variable using selectedvalue from a disconnected slicer table to be used to filter a virtual table? It seems to think more than one value is being returned in the variable, when it is not. If I add a default value it works perfectly. If I return the selectedvalue variable it works perfectly, showing the selected value from the slicer. But when I try to filter the table using the selected value variable it returns no data. I know this is not a new issue, I just don't understand how to get around it! Help me Alberto - you're my only hope!
In the measure we used CROSSFILTER() 2 times. Would their order matter? Like if we swapped the order of both crossfilters, will it make any difference?
Great video - I understand its an example, but If there is only one Class for every Product then why can't we use the CLASS column directly in the VALUES section of the matrix rather than creating a measure. Isn't the only disadvantage that you will have to hide the value in Totals line.
In case you have a total column with multiple products, VALUES would return multiple classes, throwing an error in the visual. SELECTEDVALUE protects your code from that error without having to write IF ( HASONVALUE (...), VALUES (...) )
If you want to see the category in every row without grouping by Category and you want to use the Matrix, you have to use a measure - in that case this technique is useful.
@@SQLBI Thanks for the great tip! I will use it in other projects, not in this one, because concatenating the two values can generate doubts when reading the data by users.
Count id based Max Date and status yesterday Friends, SAVE ME!!!HELP!!! COUNT MAX DATE with criteria. In a data slicer (year), knowing what was the last status of id_voluntario, if it is "AND" count, but I can't solve it. My table: tb_volunteers Where these volunteers come in and out all the time and each time he enters he enters a log line and the status changes to "E", but the input_date remains the first, and writes update_date at that moment, when he exits he inserts a new log line status changes "Y" and writes the update_date. Volunteer_id,birth_date, entry_date, status,update_date. I need to count dynamically according to the movement of the data slicer. And also average age of these volunteers until the data slicer date. Also know the average age of the counted volunteers.
I might be biased but I believe Alberto has the best DAX videos on You Tube.
I love these videos because, not having done this in about 8 months, it auto-triggers many DAX/PBI concepts in my memory--not only on the topic of the video.
Thank you Alberto, so amazed by all your valuable contributions. I have learned a lot from you, but at the same time, realized how little I know ;-) But i'll keep on watching you videos, to get a better grip on the more advanced DAX. So happy for your contribution!!
Crazy: So many important aspects answered in so few seconds... Thanks! Most valued videos for PowerBI!
You just saved me hours of headaches. Thank you.
This concept makes way more sense after reading your book since you could technically use a COUNTROWS(VALUES('Product'[Class])) . I didn't even think that total was responsible in this case at 3:00 for returning multiple values and generating the error.
Thank you so much for this video, I have searched severally and I see more complex solutions. Thanks for sharing and I hope to see more to help my learning
Thanks , this video also helped to demonstrate a different Matrix visual behavior from table chart and how selected value can help us again !
There is a lot to learn in this short video other than SELECTEDVALUE.
Like always, very well explained. 👍
Very nice example, thanks for the neat trick with dividing large sales numbers, I can use this right away in my reports 🙏
Very useful. I got curious about this function because Brian Julius and Melissa de Korte use it to make use of offset columns in a customized date table to make time intelligence measures much easier to write and more consistent.
Alberto’s video is never as simple as you thought. You think you would learn SELECTEDVALUE in this video, in reality, you are learning CROSSFILTER...
So true . That's what i was thinking 😅
Best channel ever on dax🎉 grazie mille
Wow... Got the answer for my issue... Was searching for 3 4 days.. Thanks alot
Thank you!!! You just gave me a great idea for improving the flexibility of my dashboards!!
I’m a new student and I want to say that I watch the presentation on Microsoft really you are funny teacher and lovely makes things easy and meeting is a dream Sir 🤞🙏🙏
This is just another fantastic video, by examples and presentation format from Alberto Ferrari :) Makes me feel like taking a lesson from a professor :) Thanks a lot.
Nice Function, good Example and Best Faculty for DAX.......
How I wish I had your knowledge. Thank you for sharing.
wow the dax is so complex that make me subscribe this channel
Very informative. Your method of explaining brings confidence to create new dax measures and columns.
Thank you very much for this great video😊
Wooow really awesome . Thank you sooo much to explain in easy to understand the Dax.
Thank you Alberto!
Brilliant explanation!
Could you explain what you said about the measure in the beginning?
Where a measure works like a filter on a column with no values?
Around 2:10
Thx Alberto. Very clear explaination.
God bless you for sharing this , Amazing
Thanks for sharing your valuable tips.
Your videos help me a lot. Thank you so much for sharing your knowledge. I eagerly look forward to more of your videos.
Can you make a video on usages and examples for cross filter?
I suggest you read this article: www.sqlbi.com/articles/relationships-in-power-bi-and-tabular-models/
Thanks. One issue is that without product class in Rows of the matrix the measure does not return the value in my case. (Values requires a companion for comparison)
Hello! Excelent video. Can selectedvalue be used in filter argument of calculate? Thank you very much.
Yes, but it's usually easier if you save in in a variable first. Because it is a scalar, you have to write a predicate expression, like table[column] = _variableWithSelectedvalueResult
@@SQLBI thanks.
Thanks for the video but would like to give my feedback. Tried as per your video but the if statement did not work ie although the sale column is zero or empty, the selected value is still showing the data. Any reason for this?
Thank you for the valuable video.
Hi, Thanks for such informative videos. I am not able to get values in the calculated column by the selected value it returns blank data in the column for example I have a measure [Req Days] = SELECTEDVALUE('Days'[Days]) Then I am creating new column with this code 5D_OSA_Required_Qty = ('BI Primary'[L3M_Sales_Qty]/90)*[Req Days] but not getting any result. pls help
Very good video. At some point you say, that bidirectional relationship is dangerous. How using CROSSFILTER makes it less dangerous, I wonder?
Because you apply only to specific calculations, when you really need it.
Good option, very useful function, thank you Alberto. I normally take the easy say, and I drop the column in values, then select "First value" which happens to be the only one. I guess that there will be any flaw under this option?
It works most of the time, but if there are multiple values you don't see a possible issue (SELECTEDVALUE may be better returning BLANK in the same condition).
Thank you very much for these types of videos, the best explanations.
Also I have discovered the use of crossfilter in this situation.
Question: I would have written this form:
calculate(
selectedvalue ('product category' [category])
, product
)
There is some advantage to using crossfilter over just putting the entire product table as a filter
Greetings!!
Thanks for examples!
Mr. Alberto, thanks for sharing this video. I have a question: Do you know why SELECTEDVALUE() does not work inside a FILTER() to slice a table?
SELECTEDVALUE works with the filter context, not with the row context. Use CALCULATE to perform a context transition if it's what you want.
@@SQLBI I think the problem is that the SELECTEDVALUE () function is not evaluated before FILTER (). Explaining further:
I'm trying to use the formula:
filtered_data =
FILTER (data, data [value]
You can use variables to change evaluation order, but it doesn't seem the case here - if you are using a disconnected table for Percentile it should have worked. There is probably something else in your model or formula.
@@SQLBI The table for Percentil is disconnected from table 'data' but it has a calculated column:
Percentil_Value = VALUE(PERCENTILE.INC(data[value],(Percentis[Percentil]/100)))
Hi Alberto thank you for your great videos. I am trying to solve function with a Parent selected input, but I want to split this selection in children. Is their a method to do that?
Awesome ! just when I needed it
Very useful ! Thank you for sharing !
Also can we just pass the expanded table as argument for accessing the dim. ???
Very well explained to a relative noob like me.
Thank you Mr. Ferrari..
I have an issue. I wanted to calculate the lowest value in a column for each year. Using the min implicit function I am able to get the minimum value. But when I put this on an animated bar chart, it displays all the minimum values of that particular year. Whereas, I wanted the lowest value per year to be displayed. Here is the code in DAX which I tried but the result is the same:
Lowest per year = MINX
(FILTER
(
Dates,
Dates[Year]),
MIN('Score board'[Lowest Total.Score]
)
)
It would be good if SELECTEDVALUE can handle with multiple selection. According to calculation condition, multiple selection might sum all selected ones or whatever calculation is it might be do the same for both selection.
You can use VALUES to get all the values selected.
7:10- many to one in a single direction.
What if there is multiple values in the particular column like 'AL', 'SL' etc... thx
Thanks Alberto nice information
What’s the difference in max, min etc. and selectedvalue if only one value is in ques
Alberto u are the best , tks :)
thank you Alberto!
How can I append data from 2 ADO analytical view.
Could you please help on the below Usecase:
In table visual... Selected columns from dimension and fact table. There is a date column from fact table used in visual.
Expected use case: in the table visual Need to pick only the latest record(max entry) for each year based on date column
Take a look at the techniques described in this pattern: www.daxpatterns.com/semi-additive-calculations/
Thanks for the video.
Is there any advantage of using ISEMPTY(fact_table) over ISBLANK([measure]) from the performance side?
ISEMPTY should be faster, ISBLANK requires evaluating [measure], which is certainly more expensive. However, actual difference depends on [measure] complexity, for a very simple measure the difference could be minimal.
why can't I define a variable using selectedvalue from a disconnected slicer table to be used to filter a virtual table? It seems to think more than one value is being returned in the variable, when it is not. If I add a default value it works perfectly. If I return the selectedvalue variable it works perfectly, showing the selected value from the slicer. But when I try to filter the table using the selected value variable it returns no data. I know this is not a new issue, I just don't understand how to get around it! Help me Alberto - you're my only hope!
Que explicação sensacional!
Parabéns
To grab category value why not use Related function?
Thanks my friend!
Can’t use it in calculated column, only in measure
In the measure we used CROSSFILTER() 2 times.
Would their order matter? Like if we swapped the order of both crossfilters, will it make any difference?
No, the order of CROSSFILTER doesn't matter when they are within the same CALCULATE.
See Remarks section here: dax.guide/calculate/
What's the opposite of HASONEVALUE? Like not in the sense that the line has no values in it, but there isn't a line at all for that category?
Use ISEMPTY ( VALUES ( table[column] ) )
as usual, the best Ferrari :)
Nice topic 👍
Very Clean perfect!
10:31- default- no values, or too many values.
So, why didn't you use "relatedtable" or "related" to get the same result?
I think the code look like more difficult than what is supposed to be!
I am confused. Please take a small table and explain with the requirement
Great video - I understand its an example, but If there is only one Class for every Product then why can't we use the CLASS column directly in the VALUES section of the matrix rather than creating a measure. Isn't the only disadvantage that you will have to hide the value in Totals line.
In case you have a total column with multiple products, VALUES would return multiple classes, throwing an error in the visual. SELECTEDVALUE protects your code from that error without having to write IF ( HASONVALUE (...), VALUES (...) )
@@SQLBI I was referring to the "value" section in the matrix not the VALUES function.
If you want to see the category in every row without grouping by Category and you want to use the Matrix, you have to use a measure - in that case this technique is useful.
Your method doesn't work when a productName being assigned to 2 Product class or a mis-spelled Product class
Thanks a LOT!
isempty vs. isblank what is the difference?
ISEMPTY check a table, ISBLANK check a scalar value.
See:
dax.guide/isempty/
dax.guide/isblank/
Sempre TOP!
Amazing thank you
🌱 Thanks.
Finished watching
Super Explain
SELECTEDVALUE only gets the value of one dimension, if I have multiple colors for a product then this method doesn't work!
Use VALUES instead, and CONCATENATEX to combine them in a single value.
@@SQLBI Thanks for the great tip!
I will use it in other projects, not in this one, because concatenating the two values can generate doubts when reading the data by users.
Informative!!
Count id based Max Date and status
yesterday
Friends, SAVE ME!!!HELP!!! COUNT MAX DATE with criteria.
In a data slicer (year), knowing what was the last status of id_voluntario, if it is "AND" count, but I can't solve it.
My table: tb_volunteers
Where these volunteers come in and out all the time and each time he enters he enters a log line and the status changes to "E",
but the input_date remains the first, and writes update_date at that moment, when he exits he inserts a new log line status changes "Y" and writes the update_date.
Volunteer_id,birth_date, entry_date, status,update_date.
I need to count dynamically according to the movement of the data slicer.
And also average age of these volunteers until the data slicer date.
Also know the average age of the counted volunteers.