Thinking about your DAX Queries like a SQL Query in Power BI

[@eziola]

Amazing! Do a whole playlist on DAX Queries. I think it is the most effective way to learn DAX / think in DAX! Thank you and keep em coming!

[@eziola]

I recently ran into an issue where I had to choose between: EVALUATE SUMMARIZECOLUMNS FILTER... FILTER... versus EVALUTE SELECTCOLUMNS( CALCULATETABLE(table, [filter condition], [filter conditoin],) Both approaches returned the same result, but were very different in memory usage. The SELECTCOLUMNS(CALCULATETABLE) seems to be the way to go.

[@gerryboy458]

Brilliant. What would be even better is if we could use an actual SQL query directly thus be able to leverage all that SQL expertise.

[@mwaltercpa]

So many benefits to this. I love that it’s in the app… I can build in the view, then materialize as a new dim table. It’s also a great way to learn DAX, and simplify the work your measures have to do in the report :) Love it.

[@soy_leche]

Would be interested to learn more on this in a slower and more detailed format. This seems to be a lot more complex than SQL for the same queries but would be good to know this.

[@Herr_Vorragender]

Upfront; I have never used PowerBI. And so I'm sitting here thinking why would I want yet another syntax to "replace" SQL? Maybe it's better or faster somehow. Or maybe I can't use SQL in PowerBI. But in the latter case, whouldn't it have been wiser to integrate the classic SQL syntax into PowerBI? I bet it has something to do with the other DAX capabilities.

[@Tom-kp2lv]

Dax is more powerful and versatile? @Pactrick, is that correct?

[@Herr_Vorragender]

@@Tom-kp2lv what does "more powerful" mean? If PowerBI connects to a MSSQL from where it fetches the data, there is absolutely no way it technically be quicker or less resource hungry. If more powerful means that it has more functions to use, then Java would be even more powerful than DAX. What do you think?

[@Tom-kp2lv]

@@Herr_Vorragender I'll defer to Patrick or others with more expertise. I only know some SQL and some Dax, my reply was just speculation.

[@GuyInACube]

DAX is the calculation expression language within Power BI. More folks understand SQL than understand DAX. But, if you are creating measures or certain things in Power BI, you have to use DAX. The transition can be painful sometimes to understand. This video is about how you can think about DAX if you are coming in with SQL knowledge. Of course, transformations should always be done further upstream. Those things should be done in SQL if possible, and assuming that's the data source. You still would have a need to create calculation measures though. Which is where DAX would come in.

[@GuyInACube]

@Tom-kp2lv it depends 😎 SQL has it's place and is further upstream. But once your semantic model is in place, you will need to create DAX measures for use within visuals. We always say to do data transformations are far upstream as possible. And, when it comes to your specific data, you should be testing for performance to see what gets you better results.

[@PeteBlake-h9v]

Thank you Patrick, explaining how to handle that pesky "Query(1,1) The expression specified in the query is not a valid table expression" message. This was driving me nuts 'cause I didn't understand why it was occurring and wasn't able to find good explanation for it. Made my day. Thanks again.

[@jurakovtun6763]

Wow, unbelievable quality of the video. The design, transitions and of course, the host! Thank you so much for your work! ❤

[@thomasivarsson1291]

Braces {} are important i noticed. Code with light colour on a light background.

[@StephanieNelson-z8m]

AH HA moment!! I've been having trouble with DAX because I think in SQL...this connects the dots for sure!

[@jessesmith4051]

The timing of this video was uncanny. Thanks so much for this.

[@GuyInACube]

BAM! 👊

[@thghtfl]

I’ve worked with a lot of tools over my career, and I’ll tell you this: you can usually tell early on that a product sucks if it’s trying to reinvent the wheel - just like Microsoft reinventing SQL, which had already become a standard in the field of data.

[@KoundinyaPulipaka]

Please let me know after getting the results. What should we do, is it only for analysis if we can utilize the results.

[@simonvaughan5654]

Amazing stuff Patrick, I always learn something new from each of your videos

[@GuyInACube]

Great to hear! Thanks for watching 👊

[@RobertLJ11]

Thank you for the video. This video came right on time. I am attempting to make the mental jump from SQL to DAX and this video is a great help. I'm looking forward to any other trips or tricks you post in the future. 👍🎉🎁

[@GuyInACube]

Awesome to hear! We also had to make that jump from SQL to DAX. It took time. When we made the connection to think about it like a SQL query - it changed our perspective.

[@lopypop]

Can you do another video using M like a SQL query? (Power Query is the place I'm most likely to think about data in terms of querying vs DAX is where I'm thinking about building measures)

[@GuyInACube]

Will take a look. It's a very different approach.

[@georgebob2268]

Awesome video. This has sparked my mind to figure out new ways to use the power of DAX!

[@GuyInACube]

Great to hear! Thanks for watching!

[@GaryThomann-CoGC]

Thank for dax studio. If you want to display a list of measures then use the following construct EVALUATE { CONVERT( MAX( 'table'[measure] ), STRING ) & UNICHAR(10) & CONVERT( MAX( 'table'[measure] ), STRING ) }

[@melodym5993]

Thank you!! It's getting close to transact SQL!! AWESOME!!!😊

[@Tom-kp2lv]

Love this (and your enthusiasm about it). One thing, it sounds a bit echo-y - like the room is very "live".

[@kostaskolotouros4345]

Awesome video. Planning to use DAX Studio more often, so thank you!

[@michaelandreini2920]

Hi, loved the video so helpful. I don't know if you have created one but I run into issues where the dataset becomes so large when pulling into the model, it takes so long to refresh. Do you have any content around optimizing the data retrieval to either get all that data or for a sub-set of that data? Thank you!

[@asn85uk]

Hello, thank you for the helpful video. I'm encountering a scenario where I require the use of a subquery. Specifically, I need to retrieve all sales from the salestbl where the customers match those in a distinct list obtained from the customer table. However, I'm facing difficulties implementing this query in Power BI due to the presence of date filters in both the inner and outer queries. While SSRS allows the use of date variables for start and end dates, Power BI seems to struggle with the date range in the inner query. Could you provide guidance on how to address this issue using DAX or M-query? Your assistance would be greatly appreciated. Thank you.

[@franciscoribeiro2030]

Can you use this as tables you create in the model or is the goal of this pure testing and analysis of data?

[@yosoylucernario]

You can use table expressions to add a calculated tables to your model.

[@elrevesyelderecho]

3:40 nice! Didn't know it! 😮

[@Ni3Bhosale]

Q: Brilliant video, I must say very informative. I write VBA macros but want to get expertise in PowerBI DAX Queries.. Is it right to say that i learn SQL queries and then start my hands on on POWERBI? please advise

[@nataschalaros418]

Is it possible to make DAX code visible (as tooltip eg) for users?

[@deeswift-z1h]

Hi Guy in a cube, Can I've amazon rds for psql , redshift and crowd twist connect to my same report How many data sources can I connect to my power bi report, do you have a video?

[@jorrellbonner5303]

This was great vid, Patrick!!!

[@dbszepesi]

I really don't want to talk about how long I have been using Rows() instead of just using the table constructor '{}' that I just learned about in this video.

[@GuyInACube]

We learn something every day! 👊

[@ikar2k]

That's so true for me too, bro. 👍😅

[@nagendrakg5062]

Q: I hope if Microsoft comes up with a tool that converts the sql to Dax then would be game changer

[@Believer_karma]

how to use power bi on MacBook Air?

[@JohnnyWinter-p8r]

Yooooo! So I've been writing DAX as a query language since way back in my SSAS days, when you had to write it in a plain text editor (no intellisense :o) in order to use it in SSRS reports. One question though... I've always used a different syntax to this - partly because TREATAS didn't even exist back in the day. Instead, I wrap my SUMMARIZECOLUMNS in a CALCULATETABLE, which then allows you to pass a psuedo-WHERE clause as a list in the 2nd argument. eg: EVALUATE CALCULATETABLE ( SUMMARIZECOLUMNS ( 'Calendar'[Month], "Total Freight", [M1] ), 'Calendar'[Year] = 2020 ) Is there anything wrong with this alternate syntax? I always found it more intuitive to write my filter predicates as a final step, more like SQL... but I'm the only person I know who seems to do this and have never figured out whether or not my method is egregious.

[@ashleyeatsmuffins]

I'm new to DAX and so can't properly answer you question. However my initial thoughts are that: 'Readability' trumps ' 'cleaner' Code' Often readability that colleagues will understand can be better than having the same result written in less lines but being less obvious what is going on - explicitly showing the predicate and even it being encapsulated in CALCULATETABLE (rather than the {} constructor brackets I just learned about in this video) makes it easier to understand. The only reason I would (sometimes) argue against this would be for performance reasons, and I'm afraid I can't help answer whether your code could be less performant than other methods!

[@ashleyeatsmuffins]

Side note, I double checked the meaning of Egregious before repeating it and found the old definition funny: ADJECTIVE outstandingly bad; shocking: "egregious abuses of copyright" SIMILAR: shocking, horrific, horrifying, horrible, terrible, awful ARCHAIC remarkably good.

[@kaushalpatel426]

this is going to be crazy.

[@ElderCruz]

It would be awesome if someday in the future you could create DAX notebooks integrated in Power BI Desktop, just like Jupyter Notebooks with Python or similar to RStudio

[@aravindsampath3223]

wow this looks amazing, cant wait to try it out

[@aleksvpYT]

Fantastic video!!! All I ever wanted!

[@alexscorer1]

i dont understand why we even need dax.. sql would be much more seamless imo

[@otavioalves813]

DAX was created specifically for tabular models. It has a different approach than sql and several advantages over it.

[@milpatel83]

DAX is terrible, quite frankly. I'd urge anyone to just concentrate on SQL and use DAX sparingly. This is my new years resolution.

[@sweydert]

Lol As a business consultant and DAX pro, I've worked with SQL pros everyday for several years. I seldom use manual SQL myself because DAX opens up a universe of dynamic, interactive reporting possibilities that are several orders of magnitude more powerful than anything you could produce manually in SQL. DAX is like splitting the SQL atom, unleashing unfathomable power. I make far more money than my SQL-centric peers because of this. If you're using DAX and not making at least twice what a SQL pro makes, you're not using it correctly.

[@antoniogonzalez3143]

@@sweydert “splitting the SQL atom”, this really encapsules the power that DAX brings, but most people don’t need that much power, or cannot use it correctly if their data model isn’t correct. DAX shines best with complex calculations

[@komanguy]

Since DAX runs in a certain filter context, Sql would have been confusing. Imagine an sql statement in what you don’t need a where statement because the context is already filtered!

[@JBartlett_DMU]

I still don't understand why we have to write EVALUATE at the beginning of every DAX query. If all queries must begin with EVALUATE, then the interpreter should automatically add it at runtime if necessary.

[@yosoylucernario]

Most of the times you'll start an expression with DEFINE, where you'll define measures and table expressions that you'll use in the EVALUATE expr ssion.

[@JBartlett_DMU]

@@yosoylucernario Then if there are no DEFINE statements, it should still assume the EVALUATE statement.

[@NestorAdrianzen]

This is bananas! 🍌🍌🍌

[@GuyInACube]

We agree! 👊

[@elrevesyelderecho]

2:35 nice! 😮😮 Didn't know it

[@GuyInACube]

BAM! 👊

[@floricapitu]

This looks amazing, thank you! ❤

[@irwinchawla7209]

Great feature!! Thanks for sharing.

[@sachinr6042]

Incredible!.. Thanks for sharing

[@Brian-vs9sd]

I would love a sql to dax converter. I'm new to dax and my head hurts.

[@l3ol3lca]

Nice! But Dax Studio now have more futures that you need.

[@LouisYassa]

I wonder why MS chose this nonintuitive DAX syntax vs just plain sql!

[@ajay22111]

Thank you so much amazing

[@UU-ry6gt]

awesome! as usual you make us very keen to research more on this topic!

[@Phoenixspin]

I wish I was cool like Patrick.

[@toulasantha]

Bring in a magic command to DAX where we can chose DAX/SQL

[@samratrsc]

This is cool

[@damn68]

Beautiful

[@HSGG_]

why record with potato..

[@ifyluv7402]

Interesting

[@Nalaka-Wanniarachchi]

Yo .DAX and SQL Duo.

[@brettyc]

DAX > SQL because of context & data model

[@mauricioalvarez4230]

No even close to SQL

[@echezonaazubike8054]

This is not SQL 😆

[@GuyInACube]

You are correct. It is DAX. Patrick was talking about how to think about DAX like a SQL query for those coming from SQL.

[@tonymiller225]

{TREAT AS} SWEET AZZ

[@trabek123]

SAR MARKET TO FULLY BULLISH HOGAYA HAI AAJ TO LOSS HOGA AAPKA XD

[@Emiliosguay]

DAX studio is dead jaja amazing new functionality

[@MikeRudzinski-n6q]

DAX Studio does a whole lot more than just writing queries. My main use is for quick and easy access to DMVs, and also looking at metrics (VPAX). Theses features are essential for large models and the Power BI query editor doesn't cover these areas.