Khaled Chowdhury

🚀 Definitive Guide to DAX Ch. 6️⃣ - Variables! Continuing our #SML journey through the DAX bible we learn how using variables can significantly elevate our data modeling capabilities. Here are 5 key takeaways I picked up from this short informative chapter: ⚡️Enhanced Readability: Variables break down complex expressions into understandable parts, making our formulas easier to read and understand. ⚡️Stepping Stones: I love the fact that I can start with basic elements or aggregations, and build up to a more complex result using variables. Checking results along the way and debugging unexpected results is a game changer for me! ⚡️Performance: Variables calculate expressions once, no matter how many times they're used, leading to potentially better performance. But also remember that variables evaluate where they are defined, not where they are used (⚠️ Beware of bringing outside variables into CALCULATE!) ⚡️Scope: it was interesting for me to learn more about the scope or boundaries of using variables in a single measure. Knowing how to nest VAR/RETURN statements, and knowing how to use multiple VAR/RETURN blocks in the same expression was great to learn in this chapter. ⚡️Improved Efficiency: Streamlining complex calculations into variables saves time in both writing and processing expressions, making our analytics faster and more efficient. 🎓Interested in learning together? Read along with us! The Definitive Guide to DAX by Marco Russo and Alberto Ferrari is an endless resource for gaining confidence in DAX. Whether you're starting your DAX journey or looking to refine your skills, this book offers invaluable insights into making the most of variables and beyond. #DAX #DataAnalysis #PowerBI #DataModeling #ProfessionalDevelopment #SML

43

6 Comments

In PowerBI, calculate gender wise retention rate https://lnkd.in/g8PyDEGH #powerbidesktop #powerbi #datamodelling #dax #powerpivot #microsoftexcel #msexcel #excel

1

In PowerBI, show the count of active projects in a month https://lnkd.in/gfKYNXYg #powerbidesktop #powerbi #datamodelling #dax #powerpivot #microsoftexcel #msexcel #excel

1

In PowerBI, show departments that did not exist in the previous month https://lnkd.in/gzcSQ7Bs #powerbidesktop #powerbi #datamodelling #dax #powerpivot #microsoftexcel #msexcel #excel

4

In PowerBI, via a calculated column formula, generate dates based on a condition https://lnkd.in/gZagQ_rU #powerbidesktop #powerbi #datamodelling #dax #powerpivot #microsoftexcel #msexcel #excel

1

In PowerBI, create a concatenated string of unique names https://lnkd.in/ghrPxQty #powerbidesktop #powerbi #datamodelling #dax #powerpivot #msexcel #excel #microsoftexcel

2

In PowerBI, search for a keyword in a description and bring over the category from another table https://lnkd.in/dtr4Gntc #powerbidesktop #powerbi #datamodelling #dax #powerpivot #microsoftexcel #msexcel #excel

1

In PowerBI, calculate sales variance between the sales on the current date and last known date https://lnkd.in/gnYxS8tn #powerbidesktop #powerbi #datamodelling #dax #powerpivot #microsoftexcel #msexcel #excel

1

Have you ever wanted to test how long a #PowerQuery takes to refresh? ⏱ Instead of using a stopwatch, Monkey Tools has an easy-to-use interface to let you time the refresh of individual queries, or the full Refresh All operation as needed. Even more, you can toggle your privacy settings to see the effect privacy has on those same refresh times! In this video, I introduce Monkey Tools' Time Sleuth, how it works and what you can expect. If performance is a concern in your queries, you're going to want this Monkey on your team! See how: https://lnkd.in/g3JgEuxr #Excel #DataModeling #DataAnalysis #DataAnalyticsTools #BusinessIntelligence

5

3 Comments

In PowerBI, calculate monthly cumulative costs https://lnkd.in/g5MSx8c8 #powerbidesktop #powerbi #datamodelling #dax #powerpivot #microsoftexcel #msexcel #excel

1

In PowerBI, calculate the minimum amount spent in the same month of the previous 3 years https://lnkd.in/grM-Etik #powerbidesktop #powerbi #datamodelling #dax #powerpivot #microsoftexcel #msexcel #excel

2

📆 This week we're "Working with Tables" in Chapter 1️⃣2️⃣ of The Definitive Guide to DAX. I definitely experienced some enlightening moments as I worked through the chapter! 💡 ⚡️ CALCULATE vs. FILTER: CALCULATE applies filter context through table filters before evaluating the expression, whereas FILTER iterates over the table argument before the expression. They function differently, however both commonly combine both row and filter to achieve the desired outcome. ⚡️ ADDCOLUMNS: A convenient iterator that enhances an existing table. It's often used in conjunction with CALCULATE or a measure reference to facilitate context transition during evaluation. ⚡️ SUMMARIZE: Functions similarly to a pivot table in DAX, allowing us to group by unique combinations of column values in a table expression. It's commonly paired with ADDCOLUMNS, as adding a DAX expression directly within SUMMARIZE is strongly discouraged. ⚡️ CROSSJOIN: This function creates a Cartesian product, representing all possible combinations of column values. It's particularly useful for generating a table of value combinations across different tables in our model. I particularly enjoyed the example in the book where two variations of an OR condition are created using SUMMARIZE and CROSSJOIN. The choice between the two depends on the table size and effort to scan. With a large fact table, SUMMARIZE might be slower than CROSSJOIN, which accesses dimension columns directly. These insights are just a few highlights from this chapter. Other table functions like UNION, INTERSECT, EXCEPT, and SELECTEDCOLUMNS, with practical examples, show how versatile and efficient using tables can be in our model. If you're also learning from The Definitive Guide to DAX, share your thoughts in the comments! Next week we tackle Chapter 13 on Authoring Queries! #MicrosoftFabric #PowerBI #DAX #SML #DataModeling #BusinessIntelligence

53

4 Comments

In PowerBI, count the distinct ID's based on the latest status https://lnkd.in/g5F6ArhQ #powerbidesktop #powerbi #datamodelling #dax #powerpivot #microsoftexcel #msexcel #excel

1

In this article about multi condition logic in #PowerQuery, I explore a scenario where we need to choose between several options. In #Excel, we would probably separate these options into a separate table, then use VLOOKUP to pull the appropriate value into the table. However, because Power Query doesn't have a VLOOKUP function, I build a CHOOSE function (or SWITCH if you prefer #PowerPivot’s #DAX version). 💡 See how it's done: https://lnkd.in/gqS6Xp4D

15

🚀 This week, we delve into Advanced DAX Calculations in Chapter 1️⃣6️⃣ of the DAX bible. Here are some notes📝 and key takeaways from this insightful chapter: ⚡️ The chapter shares many great Advanced DAX examples. However, its purpose is less about DAX patterns and more about learning the art of “thinking in DAX” 🤔. At this point, DAX is more than basic measures; it’s about how different variations of measures bring different levels of flexibility and performance. ⚡️ Wondering if adding measure complexity is worth it? We discover that it “depends” 💵. The chapter encourages creating test queries that compare data size upfront as an indicator of potential performance gain. Ultimately, testing your specific scenario is necessary to point you in the right direction. ⚡️ Nothing improves performance like pre-computed values. One example involves pushing reference values back into a physical table. While this table may add a little size to the model, the benefit of a LOOKUP table could dramatically improve performance by shifting the burden of a dynamic query back to a static table. ⚡️ In a later example, we learn why creating a dynamic table reference is “one of the most powerful and useful techniques in DAX.” ⚡️ Another pre-computed technique involves pushing complex time intelligence logic back to a calculated column in the date table. Rather than asking our measure to dynamically recompute comparable prior year dates to filter, the book offers a useful way to shift the burden back to a column in the model. ⚡️ It’s tempting to write a measure that iterates an IF condition over each date row in our table argument. While this may initially seem like a direct and logical approach, we should be aware of the added expense of context transition and consider if row-by-row evaluation over each date is necessary. The example shares how an improved version avoids a complex iteration and uses CALCULATE to apply filter context over the model to produce the correct amounts for dates filtered in the visual. 🎉 I encourage anyone interested in leveling up in DAX to work through the Definitive Guide to DAX. This chapter leads well into Chapter 17, The DAX Engines. Both chapters are excellent introductions to learning more about DAX optimization. If you are also reading this book, I’d love to hear what has helped you think in DAX. Thanks for reading! #DAX #PowerBI #SML #MicrosoftFabric

72

2 Comments

In PowerBI, via a calculated column, fetch the last version of software installed from another table https://lnkd.in/gx7Rtxhg #powerbidesktop #powerbi #datamodelling #dax #powerpivot #microsoftexcel #msexcel #excel

1

In PowerBI, fetch the record of the second highest date https://lnkd.in/gEbdXs6a #powerbidesktop #powerbi #powerquery #microsoftexcel #msexcel #excel

1