dax

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…

Recreates the NETWORKDAYS function from Excel with some bonus measures and one that needs fixed or demonstrates a bug/documentation glitch. NetWorkDays calculates the number of days between two dates sans weekends. NetWorkDays = VAR Calendar1 = CALENDAR(MAX(NetWorkDays[created date]),MAX(NetWor...

In this post I am going to demonstrate how to create a Dynamic TopN slicer using a What-If Parameter. This will allow your user to simply use the Slicer/Slider to view the TopN values and as a bonus if the user slides it to zero, it will display everything! Who doesn't like something that is easy to...

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...

It is Friday, and I thought of writing a quick tip on how you can use the same date dimension for displaying the last N months (say, in a bar chart) and the data for selected month (say, in a card …

Give this a shot EHT = VAR myupper = [upper] VAR mylower = [lower] RETURN AVERAGEX ( VALUES ( 'STA Core'[Total TouchTime for the record] ), CALCULATE ( AVERAGE ( 'STA Core'[Total TouchTime for the record] ), FILTER ( 'STA Core', ...