Home

😁 I want to continue to have a more detailed framework for the query language branch.

Imaging I have several tables to extract data, what data do I want?

💪 The most frequent steps are:

Step 1. Choose tables with FROM

Step 2. Add conditions with WHERE (=, non-equals, BETWEEN, IN, LIKE)

Step 3. Choose columns with SELECT and simple aggregation {COUNT, MAX, MIN, AVG, SUM, DISTINCT}

Step 4. ORDER BY to sort out the rows and LIMIT to choose the specific amount from the beginning

👉 The possible scenes are:

Option 1. Merge columns into one table with JOIN {LEFT, SELF, FULL, CROSS} and ON (=), USING; Merge rows into one table with UNION {INTERSECT, EXCEPT}

Option 2. Classify all rows into groups for calculation and comparison {GROUP BY, HAVING}

Option 3. Add new aggregated or transformed columns in temporary WINDOWs and add new values to each row, WINDOW can be one if ignores the PARTITION BY

• Aggregation functions MIN / MAX/ AVG/ SUM (…) OVER(PARTITION BY…)

• Ranking in groups RANK()/ DENSE_RANK() OVER(PARTITION BY… ORDER BY…)

• Move the value up or down LEAD/LAG (…, offset) OVER(ORDER BY…)

• Create a WINDOW related to the current row OVER(ORDER BY… ROWS/RANGE/GROUPS BETWEEN {n /UNBOUNDED PRECEDING, CURRENT ROW} AND { n /UNBOUNDED FOLLOWING, CURRENT ROW})

💁‍♀️ The special techniques are:

Technique 1: Use () to combine queries, called subquery, the latter can use the output of the former one

Technique 2: Create Common Table Expression (CTE) to create a temporary table for simplification WITH table_name AS (……)

Technique 3: Define a Window then OVER it in SELECT clause {WINDOW window_name AS (PARTITION BY…)}

✌ With this, I can solve problems by combining them together. The more questions I solve, the more functions I will know, and the framework will be enriched.

😘 At the beginning of another rainy week. I want to continue introducing the Power BI service.

🤔 𝐖𝐡𝐲 𝐝𝐨 𝐰𝐞 𝐧𝐞𝐞𝐝 𝐭𝐡𝐞 𝐏𝐨𝐰𝐞𝐫 𝐁𝐈 𝐬𝐞𝐫𝐯𝐢𝐜𝐞? Publish reports from the Desktop to the Service means moving reports from local to shared networking, others can also utilise our work.

💁‍♀️ 𝐒𝐨 𝐡𝐨𝐰 𝐝𝐨𝐞𝐬 𝐢𝐭 𝐰𝐨𝐫𝐤?

a. It is a web-based resource management platform.

b. It holds my workspace (highest private) and can also create APP workspaces to group and share resources under our account, each workspace can have datasets, reports, dashboards, dataflows, datamarts (premium tier: support Power Query Editor + SQL/Visual query + Report Building, provide Desktop experience), and scoreboards.

c.  Premium tier also has the deployment pipeline function which can help development by staging workspaces.

d.  These resources can be shared extensively via creating APPs.

👉 𝐓𝐡𝐞 𝐟𝐨𝐥𝐥𝐨𝐰𝐢𝐧𝐠 𝐪𝐮𝐞𝐬𝐭𝐢𝐨𝐧𝐬 𝐚𝐫𝐞: 𝐇𝐨𝐰 𝐭𝐨 𝐦𝐚𝐢𝐧𝐭𝐚𝐢𝐧 𝐚𝐧𝐝 𝐬𝐞𝐜𝐮𝐫𝐞 𝐝𝐚𝐭𝐚 𝐢𝐧 𝐭𝐡𝐞 𝐒𝐞𝐫𝐯𝐢𝐜𝐞 𝐰𝐡𝐞𝐧 𝐭𝐡𝐞 𝐝𝐚𝐭𝐚 𝐛𝐞𝐜𝐨𝐦𝐞𝐬 𝐡𝐚𝐥𝐟-𝐩𝐮𝐛𝐥𝐢𝐜?

Firstly, schedule refresh for the updated information and apply sensitivity labels for datasets if needed.

Secondly, for internal workers during the development process, grant them one of 4 roles with the least privilege to change the workspace: Viewer < Contributor < Member < Admin

Thirdly, for consumers or external users, grant them viewer roles for the published APPs.

Additionally, in one report, row-level security can make sure viewers only read the information they need.

😁 Hope this post can give you a general overview of the Power BI service and make it easier to explore it. Have a nice following week.

🤗 At the beginning of August, I want to introduce the core power of Power BI: DAX (Data Analysis Expression)

🤔 𝐅𝐢𝐱𝐞𝐝 𝐐𝐮𝐞𝐬𝐭𝐢𝐨𝐧: 𝐖𝐡𝐲 𝐝𝐨 𝐰𝐞 𝐧𝐞𝐞𝐝 𝐃𝐀𝐗? DAX, as its name, is mainly for Data Analysis, in other words, it provides more flexibility to handle analysis

💁‍♀️ 𝐒𝐨 𝐡𝐨𝐰 𝐝𝐨𝐞𝐬 𝐢𝐭 𝐰𝐨𝐫𝐤?

It calculates existing data to get new data with functions.

It has three final formats: measure, calculated column, and calculated table.

• 𝐌𝐞𝐚𝐬𝐮𝐫𝐞 is like a set of procedures to calculate; it will be calculated again when it is used in the report under context.

• 𝐂𝐚𝐥𝐜𝐮𝐥𝐚𝐭𝐞𝐝 𝐂𝐨𝐥𝐮𝐦𝐧 is added to the data table for each row, it would not influence the source data but it is kept in the model

• 𝐂𝐚𝐥𝐜𝐮𝐥𝐚𝐭𝐞𝐝 𝐓𝐚𝐛𝐥𝐞 is often used to create a date table for hierarchy (the results can be analysed by year, quarter, month,  and day)

👉 𝐖𝐡𝐚𝐭 𝐚𝐬𝐩𝐞𝐜𝐭𝐬 𝐝𝐨𝐞𝐬 𝐢𝐭 𝐜𝐨𝐧𝐭𝐫𝐢𝐛𝐮𝐭𝐞?

• The most common and complex one is Analysis using aggregation, logical, date, and filter functions

• Active relationship when there are multiple foreign keys in one table with 𝐔𝐒𝐄𝐑𝐄𝐋𝐀𝐓𝐈𝐎𝐍𝐒𝐇𝐈𝐏( )

• Simplify row-level security by combining the user identity table and 𝐔𝐒𝐄𝐑𝐏𝐑𝐈𝐍𝐂𝐈𝐏𝐀𝐋𝐍𝐀𝐌𝐄( )

🤓 𝐈 𝐰𝐢𝐥𝐥 𝐧𝐨𝐭 𝐥𝐢𝐬𝐭 𝐞𝐯𝐞𝐫𝐲 𝐟𝐮𝐧𝐜𝐭𝐢𝐨𝐧 𝐛𝐮𝐭 𝐬𝐡𝐚𝐫𝐞 𝐡𝐨𝐰 𝐭𝐨 𝐥𝐞𝐚𝐫𝐧 𝐢𝐭 𝐞𝐟𝐟𝐢𝐜𝐢𝐞𝐧𝐭𝐥𝐲.

Tip 1: Have a look at the official document (link: https://lnkd.in/e5nFmMH8) or Learn path (link: https://lnkd.in/ewSGXZyj). The document classifies DAX functions and provides examples, we will have a general idea about what functions they have.

Tip 2: Each function will be explained clearly in the 𝐓𝐨𝐨𝐥𝐭𝐢𝐩 when entering ‘(‘ after the function, read what it is and what parameters it needs.

Tip 3: Variables can be brought in when the measure becomes complex.

😊 Happy Power BI.