Power BI

If you have a term table like so (let's call it SecTerms): And then another table with a text column (let's call it Companies) like so: Then the following measure will work in a matrix: TermInstance = SUMX(SecTerms, SUMX(Companies, SecTerms[Weight]* (LEN(LOWER(Companies[De...

I am trying to do some advanced DAX/PowerQuery equations and I am coming up short and looking for some help. I have a dataset like the below and the use case is to get an average amount of time spent for each 'code'. To come up with that I need to take each true value timestamp and comare it agai...

Hi, Trying to work out the DAX measure to count the rows if the sum of the amount is greater than 0 per ClientID, but to no avail. Data is as follows: ClientID Amount 1 0 1 10 2 0 3 100 Each row is a meeting, and I need to count the number of meetings in the case that the sum of the amount p...

Maybe this: Rolling 3 Month Total = CALCULATE ( KPI_Finance_Actual_Budget[Actual Profit], DATESINPERIOD ( KPI_Finance_Actual_Budget[FullDate], LASTDATE ( PREVIOUSMONTH ( KPI_Finance_Actual_Budget[FullDate] ) ), -3, MONTH ) ) Although if "KPI_Finance_Actu...

There are two themes for this month’s Power BI Desktop update. The first is a focus on some of your major asks including multi-selecting data points across multiple charts and syncing slicer values across multiple pages of your report. We also have a major update to our SAP HANA connector. Our second theme is a focus on performance improvements across three different areas of our product.

DaxStudio - DAX Studio is a tool to write, execute, and analyze DAX queries in Power BI Designer, Power Pivot for Excel, and Analysis Services Tabular.

In my last post, I explored a less-than-ideal way to approach the lack of object-level security in Power BI. That workaround used DAX to mask sensitive values for certain users. Since that method i…

I needed to quickly create a calculation that tested for the presence of a string in another column's text values. There are two ways to solve this, but the first one is an interesting example that you can use in several situations. This solution plays on the true-false values of the DAX IF function in PowerBI.

During the preparation for delivering a Power BI training session for a client, I was looking at the Tabular Data Model that was their data source, and I was struggling with a long running query. I…

How do you optimize a DAX expression? In this session we analyze some DAX expressions and Tabular models and we will look at how to optimize them through th

ALLSELECTED is a powerful function that can hide several traps. This article is an in-depth analysis of the behavior of ALLSELECTED, explaining shadow filte

Introduction My recent foray into solving complex numerical/engineering problems with DAX such as Kaplan-Meier Survival Curves and Linear Interpolation led me to wonder what other kinds of problems I might be able to solve using Power BI and DAX. This inquiry led me to remember one of my fav...

Every once in awhile, I encounter questions from a trainee or client who is familiar with Tableau but who is getting started with Power BI. They have the ability to do X in Tableau and want to know…

Hi everyone, Apologies for the delay. Here are the steps I used: 1.Create a supporting table. All my revenue groups were listed in the first column and in the second column i contained what type that revenue group belonged to (See Pic) 2. Then i created the following measure based on the ab...

Hi, You may download my solution from here. Hope this helps.

Sorry I was away for a while Try this = CALCULATE ( VALUE ( MEDIAN ( Table1[Age] ) ), ALLEXCEPT ( Table1, Table1[Company], Table1[Gender] ) )

This multi-part tutorial blog is geared to help BI Analysts create versatile Scorecards with KPIs as well as to facilitate the business reporting transition from Excel, SSRS or other apps to Power BI Desktop. In this initial installment, I will illustrate how to include more than one aggregation a...

Often we use the Power of DAX statements to create complex calculations that provide tremendous insight into our data. But sometimes there are calculations that seem easy from an Excel point of view, but not possible in DAX. Excel provides the great possibility to create formulas referencing other cells or even ranges that reside in remote spreadsheets. One kind of this calculation type is the iterative application of an interest rate over a timeseries to an investment where the amount of interest adds to the investment in the next period. This means, you have to use the concept of compound...

Try this MEASURE MRBR = AverageX(ALLSELECTED(data1[TimeSPC]),[MovingRange])

A What If Parameter allows for the visualization and analysis of the effects of a change in a variable. The example I use in this post is a salary cap projection. A fictitious sports team has historic…

How to Master SUMX in DAX. Master SUMX using these simple techniques shown using examples. This makes DAX iterators easy! NFORM +61 8 7228 0515

, Change %v to the following. %v = IF(ISBLANK(MAX(tbl_SalesDaily[previousweekofyear])),0,[DIFFERENCE]/MAX(tbl_SalesDaily[previousweekofyear])) Regards, Lydia

Try this MEASURE % of category = DIVIDE ( COUNT ( TableName[Subcategory] ), CALCULATE ( COUNT ( TableName[Subcategory] ), ALLEXCEPT ( TableName, TableName[Category], TableName[Week ] ) ) )

Hi I've added in the additional checks for those columns (highligted the changes in red-bold) Parallel = VAR MyDate = DATE('Table'[year],'Table'[month],1) VAR SumOfMonthLastYear = SUMX( FILTER( 'Table1', 'Table'[year] = EARLIER('Table'[year]) - 1 && ...

This quick measure returns the selected date range formatted in accordance with the Wikipedia Manual of Style. The date format can be customized by changing the parameters listed below. NAME: Date range DESCRIPTION: Write out the selected date range. PARAMETERS: Name: Date Tooltip: Calendar ...

In today’s post, I am sharing a follow-up to my webinar, that will allow you to refresh the queries, and enjoy the support of scheduled refresh on Power BI service. During the webinar (you can watch it here, if you missed it), I created a custom function that paginates over multiple pages, and was asked …

, Drag [State] from the calculated table to Axis and measures below to Value. Measure = VAR s = SELECTEDVALUE ( 'Table'[State] ) RETURN IF ( s = "Total", SUM ( ValueTable[Regular jobs] ), CALCULATE ( SUM ( ValueTable[Regular jobs] ), FactTable[State] = s ) ) Mea...

Have you ever wanted to create automatic forecasts based on historical information? In the past, this was relatively difficult to do using tools like Excel but I’m going to show you in this video t…