Power BI

Technical articles, content and resources for IT Professionals working in Microsoft technologies

Technical articles, content and resources for IT Professionals working in Microsoft technologies

Technique to do correlation in DAX with thanks to  for teaching me about VAR statement. This creates a correlation between forecasting table and wages tables:   In forecasting, create the following custom column: CorrelateX2 = // Gregory J Deckler - 2/18/2016 // Create a new column that subtracts...

Yeah actually my bad on the ALLSELECTED function, the behaviour is quite different from what I thought it was. Cool you found a soltion.   For the other one, can you change "MAX(DegreeConferred[TERM_END_D​ATE]))" to the equivalent field in your term table? That way it should have a value even when t...

Sales_OldCustomer = CALCULATE ( SUM ( Sales[Amount in USD] ), FILTER ( Sales, YEAR ( Sales[Date] ) RELATED ( Customer[Created Year] ) ))Sales_NewCustomer = CALCULATE ( SUM ( Sales[Amount in USD] ), FILTER ( Sales, YEAR ( Sales[Date] ) = RELATED ( Customer[Created Year] ) ))

My friend and coworker Melissa Coates (aka @sqlchick) messaged me the other day to see if I could help with a DAX formula. She had a Power BI dashboard in which she needed a very particular interac…

DAX calculations can leverage relationships present in the data model, but you can obtain the same result without physical relationships, applying equivalen

Easy way to create typical finance reports like Profit and Loss using DAX that (unlike other solutions I've seen so far) can be handled with very basic know

Recently I was helping someone on the Power BI community who wanted to only see the sales for the last 12 months in his chart, nothing more. Well this is pretty simple using DAX and I decided to us…

I wish I had an awesome intro for this.  I was mostly excited to write this entry… though, the techniques are fairly well known.  I totally have no problem repeating good techniques, because… you are going to forget you read it in some other blog 2 years ago anyway   But as I got into …

Introduction I was wondering whether I could add predictive analytics into my PowerPivot Workbooks. If I could predict the sales base...

I was extremely pleased to see that there was a Crossjoin() function in DAX, if only because it meant that I wouldn’t have to rename my company. Let’s see how to use it… The Crossjoin() function re…

Following on from my last post, let’s now see how we can do group-by style in DAX queries. The key to aggregating data is the Summarize() function, which is broadly similar to a Group By in SQL. So…

Information about Microsoft SQL Server Analysis Services (SSAS) 2005/2008 and MDX - News, Blogs, FAQs, Tools, Articles, Software, Books, Learnings, Webcasts, Scripts

In this article, Alberto Ferrari describes a new efficient way to compute returning customers in DAX thanks to an idea suggested by a student attending an O

Today’s post is about different ways to set a simple filter in DAX. Although there are plenty of writings covering these patterns, I still see DAX authors get confused on a regular basis. In …

A few days ago I published the article FILTER vs CALCULATETABLE: optimization using cardinality estimation , where I try to explain why the sentence “CALCULATETABLE is better than FILTER” is not always true. In reality, CALCULATETABLE internally might

Posts about DAX function ADDCOLUMNS written by digvendra

Creating a copy of a column with a new name is pretty simple in DAX: you can just use the AddColumns() function. For example if we take a model with the DimProductCategory table from Adventure Work…

Hey guys,   I am using PowerBi in my company to create some dashboards, and I need to incorporate a calculated column in my data set that calculates the following : 1/ The difference in Days between column [StartDate] and [EndDate] 2/ However if the [EndDate] row is empty, it should use (Today) as a...

Guest post by David Churchward Gantt Hours measure debugging with DAX Studio – isn’t it pretty! At the end of my last post in the Gantt Chart series, GANTT

Try this... Total in Previous Period 3 = VAR thisperiod = MAX('Table'[Index]) RETURN CALCULATE ( [Total Measure], ALLEXCEPT('Table', 'Table'[Customer]), 'Table'[Index] = thisperiod - 1) I think these are the results you are looking for...

All measures - the last one gives us the right number for the grand total (i hope that's what it does!)

newDuration = VAR TotalSeconds=SUMX('Sheet2 (2)',HOUR('Sheet2 (2)'[CopyDuration])*3600+MINUTE('Sheet2 (2)'[CopyDuration])*60+SECOND('Sheet2 (2)'[CopyDuration]))VAR Days =TRUNC(TotalSeconds/3600/24)VAR Hors = TRUNC((TotalSeconds-Days*3600*24)/3600)VAR Mins =TRUNC(MOD(TotalSeconds,3600)/60)VAR Secs = MOD(TotalSeconds,60)return IF(DAYS=0,"",IF(DAYS1,DAYS