USERELATIONSHIP in calculated columns
HTML-код
- Опубликовано: 26 июл 2024
- Learn why USERELATIONSHIP is easy to work with in measures, but it can be challenging and give you inaccurate results when used in calculated columns.
Article and download: sql.bi/11721/?aff=yt
How to learn DAX: www.sqlbi.com/guides/dax/?aff=yt
The definitive guide to DAX: www.sqlbi.com/books/the-defin... 
Наука
![MAF Teeski - Whole Thing (feat. VonOff1700) [Official Music Video]](http://i.ytimg.com/vi/SHWEj-ndz6g/mqdefault.jpg)
Niccolò Paganini was so great at violin, some thought he sold his soul to the devil in exchange for his mastery. And of course he didn't mind mesmerizing the audience by playing technically impossible pieces. He enjoyed a bit of showing off, after all he was the master of his craft! and a bit showing off didn't kill nobody. Hats off to him and Alberto as well!
indeed this is a virtuoso show worthy of Paganini! Very interesting and useful as an opportunity to improve understanding of row and filter contexts
Thank you, I'd puzzled why my use relationship hadn't worked before in a calculated column and thought it was to do with the order so this is really useful as a deep dive to understand the order of precedence and exactly why I'd had issues.
Thanks again! Great explaination! It is the second time in a month I am stucked with DAX and your video solves my problem!
My Dax level has incresead considerably after meeting SQLBI and reading your book. Thanks, Alberto. 🥰✍🏻
Great explanation. But now I know how Einstein's students in his advanced cosmology class felt when he lectured on and explained general relativity.
Amazing lesson/video. One thing I can say to those who might have felt lost, read the Definitive Guide to DAX, it will make things a lot easier to understand.
Excellent lesson. The explanation how CALCULATE is executed just blew my mind. 👍
I have had this issue many times and always had to find different way. Now I know what was wrong with the code.
It’s a very useful video. Thank you! 👏
Complex but so carefully and well explained, like a guided tour of each potential pitfall to avoid. Further fines my understanding of the role context transition plays and how different functions rely either on row or filter context (I wish DAX somehow indicated that), and the importance of Calculates order of operations. Thank you! 🙏
Now I understand how the Italian built monumental roads, bridges and conquered the whole known world. You just need a few clear minds like Alberto's. Joke's apart, great video.
"No, don't do that because it is too complex" would have been enough, but as engineers probably we like to understand why not :)
Have you guys ever applause yourself?
I think people should also do, Here I am 👏👏👏👏👏👏👏👏👏
Brilliant, that is the most I have ever learned from doing something the wrong way.
Oh needed to watch this twice dax really challenges to think unlike other language. Thanks for the video🎉🎉🎉
Excellent Touching the Heart of Dax
Thank You, A lot to learn from your tutorials Sir.
Mind blown🎉❤️ thanks for explaining in a neat way🙏
This is a fantastic example. Well done 👏🏻
thank you for your lecture. I always find it the most difficult and also important one to understand the order of DAX evaluation in filter context and context transition.
I've got 11 words for you:
"DAX Debugger. Select row and column values to view. Step through."
This video's exercise, available in calc'd column mode, measure mode, and DAX Query mode, would be an excellent add-on to either the core product, DAX studio, or as an external tool. I'm sure I'm not the only one to suggest this.
Of additional importance is an article on default strategies for role playing dimensions such as these for beginners, intermediates, and advanced users. I think you wrote an article on it, but I can't remember. Good work.
We do not have a specific article about role-playing dimensions, it's a good idea. The topic is mentioned in other articles, though. For example in this blog post: www.sqlbi.com/blog/marco/2014/10/07/questions-and-answers-about-ssas-tabular-models-ssas-tabular/
The DAX debugger is a huge investment, hard to see in a free open-source product. Moreover, the real doubt is that very few people would be able to use it, because without a visualization of the filter context state in each step, it's hard to understand what is going on. The doubt we have is that a classical debugger would not be effective, and nobody has an idea for a good design that would be easy enough to use (and understand) to many users.
A justification we heard is that a DAX debugger would make it easier to learn DAX. This is a different point of view. A simplified learning environment where one can "see" the steps executed in a guided (and limited) DAX expression is a much simpler task, even though still very expensive in absolute terms. DAX.do is a step in that direction (learning tools) but it's hard to imagine we'll implement a similar feature there. It's simply too expensive.
Love the simple thought process for complex Dax concept. Wish you can do video about calculations of weighted average cost of inventory 🥰
Amazing as usual
Finished watching
Thanks Alberto
Wow!
That's why DAX is beautiful and evil(dangerous) at the same time!
If the sales delivery column is a Date type column I take it you could just use YEAR(Sales[DeliveryDate])) if you only need the year number and not also the prefix 'CY'?
Alternatively here is another way of doing the non LOOKUP version you show in the video/article: (just for the fun of it).
CALCULATE (
DISTINCT ( 'Date'[Calendar Year] ),
TREATAS ( { Sales[Delivery Date] }, 'Date'[Date] ),
REMOVEFILTERS ( 'Date' )
)
if the 'Date'[Date] column is flagged as a unique key column you could even skip the REMOVEFILTERS part.
Thanks for the videos and articles.
Yes, you can use the YEAR function, but RELATED is more efficient whenever you need to retrieve data from another table compared to LOOKUPVALUE or to your CALCULATE, which is almost what LOOKUPVALUE does internally.
Hard to follow to be honest... but I think I got the idea. I would appreciate more examples to see how transition happens. Thanks for the video.
Hi. Is there a way that I could pass my date parameter (from slicer on the front-end) to use in SUMMARIZECOLUMN? The goal is to create a new table that has been filtered by specific date range, chosen by the user dynamically.
Great stuff. Thank you! I wonder however if CALCULATETABLE returns empty table that filters Date table, should not Date table return a value, not blank?
If CALCULATETABLE returns an empty table, the effect should be that SELECTEDVALUE ( 'Date'[Year] ) returns blank.
Hi, I have a table with home team,scorecard, away team.. now I want a slicer of teams selecting one displays all the matches played by that team in home n away. Creating a separate table "Teams" n linking them makes one active n other inactive relationship.so I get either all home matches or away matches according to active relationship. Is thr any solution?
can u explain where we can use quickbooks in power bi
Uggg, why is Dax so hard...
Amazing! explanation Alberto :). Thank you so much for this kind of videos.
I had some images in mi mind of how this used to work, but as almost always!! the results indicates if the query is ok or not and then, to the next, lol. Now i realize the very importance of the knowlege about the precise order and the precise steps too of the CALCULATE, this is absolutely key.
However, in terms of performance what would you think?, if for example my lookup table was a big big table instead of date table for example, Is it worth to code the CALCULATE USERELATIONSHIP or not?, i know of course it will depends almost always from many factors, but in your wide experience ??
NOTE: The calculated columns i'm refering here are from writing DAX in DaxStudio, not inside SSAS or PowerBI Models
It depends - measure alternatives, but USERELATIONSHIP usually results in better performance compared to LOOKUPVALUE.
😲🤪 Nice! Rgds
Awesome, Can we write same formula in Power BI Desktop. using Lookup Value ?
Yes, but LOOKUPVALUE doesn't use relationships.
@@SQLBI Thanks
wt 's the diff between EOMONTH AND end of month
Look at dax.guide/endofmonth/ and dax.guide/eomonth/
Alberto :-) link to file is messed up , Please corect it
The sample file is now available - thanks for reporting it!
I have downloaded and installed a gateway which by default is my gateway cluster and through which I published and refreshed a report. Now I have created another report with a new data source but unable to refresh it as while adding the data source to to the previously downloaded gateway cluster the error message appears "not configured correctly". Do I need to download another gateway and add to existing cluster for the data source to get refreshed? If yes, then will I have to download the gateway again and again for every report that I create and add to the gateway cluster? Also what will happen if other colleagues of my organization start preparing the report on their own computer? Will they have to repeat the same process? Or they can use my gateway cluster and keep adding the new gateways to it?
The question is not related to this video - use this forum instead: community.powerbi.com/t5/Service/bd-p/power-bi-web-app
Alberto has a very dry sense of humour!