Ian Wang

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

2

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

1

Pivot Table - Tips and Tricks| How to extract source file automatically| Short cut #excel #microsoft #microsoftexcel #office #word #o #powerpoint #business #cursodeexcel #data #msexcel #curso #cursoexcel #powerbi #exceltips #motivation #datascience #microsoftoffice #n #aprenderexcel #planilhas #dicasexcel #windows #dashboard #cursos #ado #exceltraining #like #excelbasico #dataanalytics

5

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, calculate monthly cumulative costs https://lnkd.in/g5MSx8c8 #powerbidesktop #powerbi #datamodelling #dax #powerpivot #microsoftexcel #msexcel #excel

1

Did you know you can create your own functions in Excel?? In this weeks video I’ll show you how! https://lnkd.in/ecj6WsiB

123

6 Comments

𝐃𝐚𝐲 𝟑: 𝐓𝐫𝐚𝐧𝐬𝐟𝐨𝐫𝐦𝐢𝐧𝐠 𝐃𝐚𝐭𝐚 Today, I took a deep dive into Power BI's data transformation capabilities using Power Query Editor. Transforming data is a crucial step in preparing it for analysis, ensuring that it is clean, accurate, and structured correctly. Here's what I learned and achieved: #day3 Transforming data is all about preparing it for the next stages of analysis and visualization. With these skills, I'm now better equipped to handle messy data and turn it into a clean, usable format that drives meaningful insights. Stay tuned and follow Souvanik Chaudhury 🇮🇳 for more insights from my Power BI journey. #PowerBI #DataVisualization #Interface #day3 #100dayspowerbichallenge #souvanikchaudhury

28

4 Comments

In PowerBI, perform a conditional "Remove duplicate rows" operation https://lnkd.in/duU6jU7G #powerbidesktop #powerbi #powerquery #microsoftexcel #msexcel #excel

1

𝗡𝗲𝘄 𝗫𝗲𝗿𝗼 𝗙𝗲𝗮𝘁𝘂𝗿𝗲: View 50 lines instead of 10 on the reconcile tab. Before you could only see 10 statement lines per page. You'd have to click 𝙣𝙚𝙭𝙩 𝙥𝙖𝙜𝙚, 𝙣𝙚𝙭𝙩 𝙥𝙖𝙜𝙚, 𝙣𝙚𝙭𝙩 𝙥𝙖𝙜𝙚... With the latest updates, this will be a thing of the past. The expanded statement lines will also play well with the search and filter function they just released.

27

🚀 Everything comes together in this final chapter 2️⃣0️⃣ where we Optimize DAX in "The Definitive Guide to DAX"!! Optimizing DAX is not just one best practice, but a process of iterating through different improvement strategies. This chapter points us to core fundamentals and sound approaches to improve performance. ⚡️Analyzing: We start by analyzing server timings, as it displays information about the entire query. We are concerned with larger materialization, longer scan times, and the need to refer back to the formula engine through CallbackDataIDs (Callbacks). ⚡️ Less is more! A key fundamental is to write measures that filter columns rather than entire tables. Not only do we reduce the need to materialize and scan unnecessary columns, but less columns often reduce rows generating a smaller table of unique value combinations. This can be a huge gain when iterations and context transitions are involved. ⚡️Avoiding full table example: When calling on non-additive expressions (i.e., DISTINCTCOUNT) the SE query might materialize a query for each row in the table I.e., every sales transaction. The improvement again is to reduce the table filter down to column filters (i.e., just Qty and Price) to avoid the excessive size of the datacache processing each sales row. ⚡️CallbackDataID: CallbackDataIDs occur when using IF (in an iterator), DIVIDE, scalar functions used in an iteration that don’t aggregate I.e. DATE, VALUE, rounding etc. There is a processing cost and a missed opportunity to save the datacaches. ⚡️Avoiding Callbacks with CALCULATE! ⭐️IF conditions involving iterators require the FE through callbacks. A CALCULATE filter is generally preferred over a row-by-row IF conditional test. ⭐️DIVIDE and using IF to test for non-zero denominator values also use callbacks. However, an alternate approach uses CALCULATE with a filter to filter out non 0 denominator values to avoid the callback! ⭐️Optimizing IF-then Iterator expressions by adding two CALCULATEs together, each with an opposing filter argument simulates a dual result IF condition. A brilliant way to avoid costly callbacks. 🛟 Once again it’s CALCULATE to the rescue avoiding callbacks and keeping filters in the SE as a WHERE filter. ⚡️Nested Iterators can’t be merged into a single storage engine query. Options shared include: ⭐️Lower the granularity of the outer iterator table using VALUES over a column. ⭐️Eliminate the nested iterator by leveraging SUMMARIZE to combine two table iterators into one. ⭐️Iterating the expanded Sales table, and using RELATED in our expression avoids the expense of a measure reference that includes a hidden nested iterator and context transition! I’m thankful for those that have read my posts this year, especially those in #SML that have given me time for DAX demos. I am no expert, but I do feel more confident in DAX. Huge thanks to Marco Russo and Alberto Ferrari! This is indeed the DAX bible! The “brick” is next! #PowerBI #DAX #SML

80

8 Comments

Mapping for Data Stratification in excel | Excel for auditing & auditors #excel #exceltraining #exceltutorial https://lnkd.in/g8C4T57Q

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

54

4 Comments

In PowerBI, create one row for each day which falls between a start and an end date https://lnkd.in/gdWMcjKi #powerbidesktop #powerbi #powerquery #microsoftexcel #msexcel #excel

1

Usage of "show value as option" in PIVOT TABLE https://lnkd.in/gDw_3t2H #EXCELTIPS

8

Commonly used Formulas - Part 3 1.3 - TOP 3 LOGICAL FORMULAS #Excel #ExcelTips #ExcelTraining #Business #SmallBusiness #Technology #Microsoft #Data #Accounting #DataAnalytics #BusinessIntelligence #Learning #Education #Elearning #OnlineLearning #KeepLearning #NeverStopLearning #DistanceLearning #LifelongLearning #FunLearning #ExcelVBA #ExcelFormulas #DataVisualization #Dashboard #DataScience

9

#30secexceltips How to add data to multiple non adjacent cells at the same time #exceltips

4

COMMONLY USED FORMULA - PART 1 1.1 - 6 BASIC ARITHMETIC FORMULAS #Excel #ExcelTips #ExcelTraining #Business #SmallBusiness #Technology #Microsoft #Data #Accounting #DataAnalytics #BusinessIntelligence #Learning #Education #Elearning #OnlineLearning #KeepLearning #NeverStopLearning #DistanceLearning #LifelongLearning #FunLearning #ExcelVBA #ExcelFormulas #DataVisualization #Dashboard #DataScience

10

In this post, we will look at how traditional PivotTables support a single data table while Power Pivot supports multiple data tables. 👉 https://lnkd.in/gqr3BZfD

1

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

1