Using the SELECTEDVALUE function in DAX
ฝัง
- เผยแพร่เมื่อ 26 ก.ย. 2024
- Learn how the SELECTEDVALUE DAX function simplifies the syntax required in many scenarios where you need to read a single value selected in the filter context.
Article and download: sql.bi/53917?a...
How to learn DAX: www.sqlbi.com/...
The definitive guide to DAX: www.sqlbi.com/...
I might be biased but I believe Alberto has the best DAX videos on You Tube.
Crazy: So many important aspects answered in so few seconds... Thanks! Most valued videos for PowerBI!
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.
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 😅
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!!
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 🤞🙏🙏
Very nice example, thanks for the neat trick with dividing large sales numbers, I can use this right away in my reports 🙏
There is a lot to learn in this short video other than SELECTEDVALUE.
Like always, very well explained. 👍
Wow... Got the answer for my issue... Was searching for 3 4 days.. Thanks alot
Thanks , this video also helped to demonstrate a different Matrix visual behavior from table chart and how selected value can help us again !
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
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!!! You just gave me a great idea for improving the flexibility of my dashboards!!
Best channel ever on dax🎉 grazie mille
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
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.
God bless you for sharing this , Amazing
Wooow really awesome . Thank you sooo much to explain in easy to understand the Dax.
Thx Alberto. Very clear explaination.
Very informative. Your method of explaining brings confidence to create new dax measures and columns.
Thank you very much for this great video😊
Thank you Alberto!
Brilliant explanation!
Alberto u are the best , tks :)
Thanks my friend!
Thank you for the valuable video.
Thanks for sharing your valuable tips.
Thanks for examples!
Thank you Mr. Ferrari..
thank you Alberto!
Thanks a LOT!
Thanks Alberto nice information
Very useful ! Thank you for sharing !
Awesome ! just when I needed it
🌱 Thanks.
Que explicação sensacional!
Parabéns
Super Explain
Very well explained to a relative noob like me.
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)
Very Clean perfect!
Finished watching
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
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!
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
Also can we just pass the expanded table as argument for accessing the dim. ???
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!!
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/
Your method doesn't work when a productName being assigned to 2 Product class or a mis-spelled Product class
What’s the difference in max, min etc. and selectedvalue if only one value is in ques
To grab category value why not use Related function?
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?
Nice topic 👍
I am confused. Please take a small table and explain with the requirement
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!
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.
as usual, the best 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]
)
)
Sempre TOP!
7:10- many to one in a single direction.
What if there is multiple values in the particular column like 'AL', 'SL' etc... thx
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?
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).
Informative!!
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.
How can I append data from 2 ADO analytical view.
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.
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)))
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] ) )
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/
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/
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.
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.
isempty vs. isblank what is the difference?
ISEMPTY check a table, ISBLANK check a scalar value.
See:
dax.guide/isempty/
dax.guide/isblank/
You just saved me hours of headaches. Thank you.
10:31- default- no values, or too many values.
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.
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.
Amazing thank you