Microsoft Fabric Learn Together: Episode 1 - Get started with end-to-end analytics and lakehouses in Microsoft Fabric

The Microsoft Fabric Learn Together series has kicked off this week. This means you can prep for the new DP-600: Implementing Analytics Solutions Using Microsoft Fabric exam with the guidance and support of experts, live and in real time, for free!

You can complete the Microsoft Learn modules in your own time by following the 'self-paced' option (scroll down after clicking the exam link above), or you can register for the Microsoft Fabric Learn Together series.

Personally, I find it difficult to focus and learn on my own, so am really enjoying the Learn Together series. I also find it helps when I write stuff down, so I'll be using this blog to capture my own thoughts and key takeaways from each session. 

If you missed the first few, don't worry, you can still catch up - just click the 'Register' button and it will take you straight to the recording. 

This episode covers two modules.

Introduction to end-to-end analytics using Microsoft Fabric

It's always good to start at the beginning - this intro reminded me just how vast Fabric is. It truly does take a team, and the 'data teams' highlighted in this module be familiar, or you may have a slightly different setup in your org. I recommend firing up your own personal tenant trial to have a play with all the features as you're going through these modules - it ensures you can explore all the options and toggle things on and off without needing to wait for that other team member to enable things. 

Once you've got stuff figured out, you'll see how all the pieces fit together and how easy it is to work as a team within Microsoft Fabric - and so customisable too!

Get started with lakehouses in Microsoft Fabric

Data lake + data warehouse = lakehouse

Get the best of both worlds - just like working remote from a lakehouse, you get the structure

My cliff notes on Episode 1: Get Started with end-to-end analytics and lakehouses in Microsoft Fabric.

My key takeaways from the Learn Together session by Heidi and Treb:

• Don't be intimidated by the jargon (aka confusing technical terms).
• 'End-to-End Analytics' means your whole analytics team can work from one place; MS Fabric is your one stop shop for data engineers, data scientists, data analysts, and decision makers.
• 'Lakehouse' is not just a holiday destination - and it's waaaay easier to build a Fabric Lakehouse to import and transform data, than it is to build a holiday home (and maybe even cheaper too?).
• While some of us wear many hats and work across lots of roles, Fabric is huge and you'll likely stumble upon stuff that looks foreign and new to you - you're not alone.
• There's multiple ways to achieve the same outcome, find a way that works for you!
• The good stuff is under the ... ellipsis menu
• Stick around for the exercise; you'll surprise yourself with how familiar some of the concepts are when you see them in action.

DAX Time Intelligence - Easy pattern to get DAX Time Intelligence functions to return the correct results

 DAX is a simple but complex language. It can be very powerful if you understand how to use it properly. In this post, we're going to look at a specific group of functions in DAX: Time Intelligence.

Time Intelligence DAX functions

Time Intelligence functions in DAX change the filter context on the Date table of your model. 

Step 0: Prepare 

Before using Time Intelligence functions, you should:

• Check out the documentation at https://dax.guide/functions/time-intelligence/ 
• Make sure your model has a Date table that's marked as such: https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html 
• Have a basic understanding of the CALCULATE function - it's crucial to this solution

Time Intelligence functions in DAX are not vital functions. We can calculate the same results without their use, just need to think a bit differently about the problem. Greg Deckler has a great post on this here: 

https://community.fabric.microsoft.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/td-p/434008

My post here aims to debunk the mysteries behind using Time Intelligence functions and provide you with an easy pattern that works every time.

Easy Time Intelligence DAX Pattern

Now that we know what time intelligence is, how do we use it?

Step 1: Create a base measure

You may have already completed this step. The base measure is what you are reporting on, it should come from a table related to your Date table. Consider the 'Example 2' in my post on Relationships in Power BI: https://excelwithallison.blogspot.com/2020/08/its-complicated-relationships-in-power.html 

We have a 'Date' dimension table and an 'Incident Reports' fact table. These are the only two tables we need to consider for this solution. 

Our base measure will be Incident Count:

Incident Count = DISTINCTCOUNT( 'Incident Reports'[IncidentID] )

Your base measure could be anything, and use any aggregate function or filters, or combination of DAX functions, as long as it returns the results you expect when put in a table/matrix with a column from your Date table. 

Step 2: Select your time intelligence function

This may be the most difficult step, and I may write a separate blog on this if I get time. For this post, I'm going to use my favorite time intelligence function: DATEADD. I like this function because it's versatile and works for a lot of the scenarios my clients ask for, without requiring me to learn /memorize new functions.

For this example, I want to compare my [Incident Count] base measure above to the same period last year. DATEADD will work nicely for this.

Step 3: Apply the Time Intelligence Pattern

Now we're ready to apply the easy Time Intelligence Pattern. 

Time Intelligence Pattern Measure = CALCULATE ( [Base Measure], FUNCTION ( 'dimDate'[Date], functionparameters ) )

What does this look like in practice? Let's apply it to our example using DATEADD and [Incident Count]: 

Incident Count Prior Year = CALCULATE ( [Incident Count], DATEADD( 'Date'[Date], -1, YEAR) )

Step 4: Use in the context of your Date table

This final step is critical, and one that can cause many well-written time intelligence functions to fail if forgotten. You MUST use this measure in the context of your Date dimension table. 

In my example, I've created an [Incident Count Prior Year] measure. If I don't put a filter for year, or use this in a table / visual split by Year, I'll just get the result of all Incidents except the maximum year in my date table (this is by design and how time intelligence functions work, something that I may expand on in another blog post). 

For many people, their date tables include future years (for budgeting and forecasting purposes), so when they create a time intelligence function correctly, it simply returns the same value as the base measure. That is, until they apply a date filter.

Use the Date dimension in any visuals or filters for this measure. 

Conclusion

That's it! You've now got the easy time intelligence pattern for DAX. It can be applied to all sorts of different scenarios. 

Additional Examples

Total Sales = SUM( Sales[Sales Amount] )

Total Sales Prior Year = CALCULATE( [Total Sales], DATEADD( dimDate[Date] , -1 , YEAR) )

Total Sales YTD (calendar) = CALCULATE( [Total Sales], DATESYTD( dimDate[Date] )

Total Sales YTD (fiscal) = CALCULATE( [Total Sales], DATESYTD( dimDate[Date], '6-30' )

Remember

Key tips:

• Create a base measure first - it makes things easier to follow
• Use the CALCULATE function with the Time Intelligence function as a filter
• Use the Date dimension table in:
• Your time intelligence function wherever it asks for a date
• Your visuals and filters whenever using the new measure

Enjoy!

Get Link to Channel: Private Teams Channel

 I've just struggled with trying to 'Get link to channel' for a Private Channel in Teams. I typically recommend against using private channels in Teams, and perhaps this is one reason. 

Not sure why, but the 'Get link to channel' functionality doesn't exist for Private Channels like it does for Public Channels. 

Even more frustrating, when you do finally find the link to the Private Channel, it doesn't work for a button embedded in a SharePoint page within Teams tab.

Luckily, with a few simple deletions, you can get this to work.

Step 1: Click the three dots at the top right of Channel 'Posts'

Public Channel Menu:

Private Channel Menu:

For the Public Channel, your job is done - click the 'Get link to channel' option and copy the link. It will look something like this:

Get Link to Channel Link

https://teams.microsoft.com/l/channel/19%3randomstringguidbtg1%40thread.tacv2/ChannelName?groupId=GUIDwillbehere&tenantId=GUIDwillbehere

For the Private Channel, click 'Open in SharePoint' first, then click the Teams icon (you won't be able to right click > copy link, so will have to actually click the link to open. This will open a brower tab initially, with an option to open Teams - cancel the dialog box. 

Copy the URL from the web browser. It will look something like this:

Open in Teams Link

https://teams.microsoft.com/dl/launcher/launcher.html?url=%2F_%23%2Fl%2Fchannel%2F19%3Aguid%40thread.tacv2%2FChannelName%3FgroupId%3guid3%26tenantId%3guid&type=channel&deeplinkId=guidDl=true&msLaunch=true&enableMobilePage=true&suppressPrompt=true

https://teams.microsoft.com/dl/launcher/launcher.html?url=/_%23/l/channel/19:2fe074ff85f149d6b02db2d8e2c2d708%40thread.tacv2/Emerging%2520Leaders?groupId%3De1ebf9f0-c485-49b1-bc91-98f1cc7a1dd3%26tenantId%3D8a587731-94b7-42f0-80c3-955f55452f65&type=channel&deeplinkId=7a17c76e-7862-47c4-a91f-7c0b455aac5e&directDl=true&msLaunch=true&enableMobilePage=true

Step 2: Edit link

We want to make this link follow the same format as our first 'Get link to Channel' link. Let's compare them side by side: 

Get Link to Channel Link

https://teams.microsoft.com/l/channel/19%3randomstringguidbtg1%40thread.tacv2/ChannelName?groupId=GUIDwillbehere&tenantId=GUIDwillbehere

Open in Teams Link

https://teams.microsoft.com/dl/launcher/launcher.html?url=/_%23%/l/channel%2F19%3A2fe074ff85f149d6b02db2d8e2c2d708%40thread.tacv2%2FChannelName%3FgroupId%3DguidtenantId%3D8a587731-94b7-42f0-80c3-955f55452f65&type=channel&deeplinkId=7c37e892-9b61-48f2-8fba-f009ebf8b1e2&directDl=true&msLaunch=true&enableMobilePage=true&suppressPrompt=true

Differences

You'll notice the 'Get Link to Channel' link is just a simpler version of the 'Open in Teams' link. To get the 'Open in Teams' link to work from SharePoint page embedded within Teams tab, simply delete the parts in bold below:

https://teams.microsoft.com/dl/launcher/launcher.html?url=/_%23/l/channel%2F19%3Aguid%40thread.tacvChannelNamegroupId%3guid3%26tenantId%3guid&type=channel&deeplinkId=guidDl=true&msLaunch=true&enableMobilePage=true&suppressPrompt=true

to end with a link that resembles the 'Get Link to Channel' link:

https://teams.microsoft.com/l/channel/19:31616bb9ae134f9ab605c0ac9635c2a1%40thread.tacv2/ChannelNamegroupId%3De1ebf9f0-c485-49b1-bc91-98f1cc7a1dd3%26tenantId%3D8a587731-94b7-42f0-80c3-955f55452f65&type=channel&deeplinkId=d2990055-682c-4183-857f-50a1a8b51087