I've been searching over and over, but I can't find the solution - so I'm afraid that there isn't any solution to this. I have a table ("Time reports") with all reporting for our employes, grouped by Person and Date. The table looks like this example:   Date Person Billed hours 2017-04-12 Joe 5 201...

“AGAIN!” -Pai Mei       SUMX() – the great iterator Have you ever written an array formula in Excel?  (Don’t worry

I was helping out in the Power BI Community and I came across the following requirement. The user had sales figures and based on the Previous month, they wanted to have an arrow showing if it was u…

I recently discussed with Darren Gosbell about how to calculate the Median in DAX. The problem is to make the calculation using a measure, so that you can use it in a dynamic way into a PivotTable. We have a People table with three columns: Gender, Customer

DAX Patterns is a collection of ready-to-use data models and formulas in DAX, which is the programming language of PowerPivot. Create your Excel data model faster by using a DAX pattern!

I am unable to change the format of a measure. The Formatting section of the Modeling tab only allows Format: Text to be selected. Here's the measure: NHSSVUNNetAssets = CALCULATE(MEDIAN(AuditNumbers[UnrestNA]),AuditNumbers[PCODE]="8217")   It's possible I am using the wrong DAX. For example, Media...

The Challenge A fairly common data model design pattern can be described such that: Given a set of criteria that are numerically scored These scored criteria are grouped together in arbitrary sets and the group scored via an aggregation of criteria scores These groups of scores are further grouped ...

Hi!   I've been spending my day banging head to the wall with this problem. I need to check if value is a duplicate of value in the same column that is filtered by slicer.   Slicer Part code Has duplicates 1 14 FALSE 1 15 FALSE 1 16 FALSE 2 14 TRUE 2 15 FALSE 2 16 FALSE 2 14 TRUE     I was going for...

I got really stumped for my whole day on how to display marketshare on Power BI. My data is kind of complicated but I will try to simplify the problem in here, assume I have two tables:   1. Dimension Supplier:     2. FactInvoice:     I would like to display the marketshare (Sum of Spend) for one ...

Greetings,   I was wondering if there was a way to display the most common value in a column? For example:   Reported_Users UserA UserB UserC UserA UserA UserA   Here UserA is the most common user. So I was thinking of trying to display it with a  "Card" visual.   I think I read somewhere it's possi...

You might have used FIRSTNONBLANK and LASTNONBLANK in semi-additive measures, but you might not be aware that their use is not limited to time intelligence

Hi   How can I represent the following using DAX. I am trying to Bucket the following Sales Quantity per Customer:   Customer Quantity A 0 A 200 A 300 B 100 C 80 C 20 D 50 D 25 D 25 D 0 TOTAL 800   and represent the following in Buckets of 3 as Top 20%, Mid 60% and Bottom 20% of the Total as below: ...

Hi,   I need to create a report that will show me realised values and estimated values for the rest of the year.   The report should be a table having finance accounts on rows and months in the columns. If the user filter the year to 2017 and month to april the first 4 columns should show realised v...

Last month, I explored how to take a Power BI area chart and make it into a unique “new” chart type using only data and DAX formulas. The line chart is also a candidate for a bit more f…

This was a problem that took me a little bit longer to solve than I expected. We’ve developed a business intelligence solution for a training...

EARLIER is a DAX function that acts exclusively on row context,  and its purpose is to create a reference to a column value on an outer loop of the evaluation of the expression.  It is commonly use…

“…then they start thinking that, ‘where there’s smoke there’s fire’ logic…” Continuing the Impromptu Series of Simple Real World Examples The Mr. Ex

Egg on My Face For Improper Use of EARLIER() Tonight I was looking at one of my recent posts, the one about “fuzzy” time relationships in calculated

Sometimes reporting data values day-by-day can create the impression of dramatic ups and downs, when really what we want to see is the general trend of changes over time.  We need, in other words, to create a chart based on moving averages.  Such a measure is commonly used in tracking the price of stocks, but […]

As I remember managers used to love SSRS reports for their availability of traffic light indicators which are useful way of measuring KPIs, trends etc. During my recent Power BI engagements with clients I have been asked few times if similar can be achieved in Power BI and my answer to that was, y...

Hello community,   I have a question related to the frequency golf players are linked to each other during a competition. Imagine players are grouped together in several flights during one day, and grouped together differently in flights the next day. This continuous during the season. Now I want to...

KPI for easy Profit and Loss in PowerBI as the last part of my series: Learn how to write them for columns and rows with minimal efford.

Hi,   I want to create a dax formula that will produce a list of unique guest values from Column A, and find only the distinct value in column B (Host) based on the latest date.  The final report will only have a list of distinct guests and one host per guest.  Although, I would expect to see multip...

Is it possible to dynamically calculate ranks based on filters? If I filter B, D, E can I see the new ranks as 1, 2, 3?

Hi Everybody   How can I write a DAX expression that show only the Sales of the last 5 five years based on the current selection of the year. For Example: -If I select 2015, the chart should show only sales for the years 2015, 2014, 2013, 2012, 2011 -If I select 2012, the chart should show only sale...

Hi all.   I am new in this community, and very dummy in Power Bi. I have a fact table with a link to a master calendar by the date. The problem I have is this: I have created a mesure this way:   Valor Euros = SUMX(FILTER( 'Hechos Hstock_valorado' ;  RELATED(Tiempo[Fecha]) = max(Tiempo[Fecha]));'Hec...

Hello,   I want to calculate the following: the average for the last 3 months and i want to project this data for the rest of the year.   For example: the data for the first 3 months is real data   jan    feb   mar     apr     may     jun    jul 10      20    30        20      20       20     20   I...