Category Archives: Basic script

Use you inactive relations with “USERELATIONSHIP” in DAX

Posted on by • Posted in Basic script • Tagged • Leave a comment

In Tabular models you can only have one active relationship between two tables. But you can have multiple inactive ones. In the image below you can see how the active relations are marked with a solid line. And then inactive ones are marked as dotted lines.

Relationsships

So how do you use your inactive ones?

I start with a basic query that gives me the sales per year. Because I did not specify a relationsship the active relationship is used. In this case the order date.

EVALUATE
 (
    ADDCOLUMNS (
        VALUES ( 'Date'[Calendar Year] ),
        "Total sales", 'Internet Sales'[Internet Total Sales]
    )
)

And the result

Relation 1

So if I want to use an inactive relationship too I need to use the USERELATIONSHIP function. Below is an example on how to get both sales per order year and sales per shipment year in the same query.

DEFINE
    MEASURE 'Internet Sales'[Total Sales By ShipmentDate] =
        CALCULATE (
            'Internet Sales'[Internet Total Sales],
            USERELATIONSHIP ( 'Internet Sales'[ShipDateKey], 'Date'[DateKey] )
        )
EVALUATE
 (
    ADDCOLUMNS (
        VALUES ( 'Date'[Calendar Year] ),
        "Total sales", 'Internet Sales'[Internet Total Sales],
        "Total sales by ShipmentDate", 'Internet Sales'[Total Sales By ShipmentDate]
    )
)

And the result will look like this

Relation 2

Count your top selling products with DAX

Posted on by • Posted in Basic script, DAX • Tagged , , • Leave a comment

One of my customers wanted to count their top selling products. Since sharing is caring I will show you how this can be achived in this post 🙂

I want to list all products that sold for at least 5000 dollars in the year 2005. 

DEFINE
    MEASURE 'Internet Sales'[Sales] =
        SUM ( 'Internet Sales'[Sales Amount] )
EVALUATE
        ADDCOLUMNS (
            FILTER (
                SUMMARIZE (
                    CROSSJOIN (
                        VALUES ( 'Date'[Calendar Year] ),
                        VALUES ( 'Product'[Product Id] )
                    ),
                    'Date'[Calendar Year],
                    'Product'[Product Id]
                ),
                'Internet Sales'[Sales] >= 5000
                    && 'Date'[Calendar Year] = 2005
            ),
            "Sum of Sales", 'Internet Sales'[Sales]
) ORDER BY 'Internet Sales'[Sales] DESC

And the result will look like this

Top selling 1

If you want to only display the number of products you can add “row” and “countrows” like this

DEFINE
    MEASURE 'Internet Sales'[Sales] =
        SUM ( 'Internet Sales'[Sales Amount] )
EVALUATE
ROW (
    "Count", COUNTROWS (
        ADDCOLUMNS (
            FILTER (
                SUMMARIZE (
                    CROSSJOIN (
                        VALUES ( 'Date'[Calendar Year] ),
                        VALUES ( 'Product'[Product Id] )
                    ),
                    'Date'[Calendar Year],
                    'Product'[Product Id]
                ),
                'Internet Sales'[Sales] >= 5000
                    && 'Date'[Calendar Year] = 2005
            ),
            "Sum of Sales", 'Internet Sales'[Sales]
        )
    )
)

And the result will look like this

Top selling 2

Percent of total in a hierarchy (DAX)

Posted on by • Posted in Basic script • Tagged , • Leave a comment

Getting percent of total in a hierarchy is easy in DAX. In this example we will use the Geography hierarchy.

Geography 1

Then I will create 2 calculated measures in my model.

TotalSales2:=calculate(SUM([Order Quantity]);ALL('Geography'))

And 

Ratio To Total:=Sum([Order Quantity]) / [TotalSales2]

The last one should be formatted as percent.

Then process and deploy your model.

If you open an Excel pivot you should see something like this

Hierarchy Excel 2

If you summarize “Ratio To Total” in this pivot you will get 100 %.

When changing the pivot to show city instead of country the percent still works as intended.

Hierarchy Excel 3

I did not show all the cities in the screenshot above

Get percent of total in DAX using ALLSELECTED()

Posted on by • Posted in Basic script, DAX • Tagged • Leave a comment

To get percent of total in DAX we can use the ALLSELECTED function. To demonstrate how this can be achieved I will make 3 calculated measures in my model.

1. TotalSales:=CALCULATE(SUM([Order Quantity]);ALLSELECTED())
2. Sum Order Quantity:=Sum([Order Quantity])
3. Percent Of Total:=[Sum Order Quantity] / [TotalSales]

The first measure will give the total quantity. The second one will give sum quantity for each dimension member. The last one will give the percent. To make the last one nice and readable you can format it as percent.

This is how it will look in an Excel pivot

Percent of total

This easy approach will only work on the first level in a hierarchy. To make it work on multiple levels you have to extend the code. But that will be an other post 🙂

Identify products with no sale in DAX

Posted on by • Posted in Basic script, DAX • Tagged , • Leave a comment

To identify products with no sale in DAX we can use the ISBLANK function.

Here is a sample

EVALUATE
FILTER (
    SUMMARIZE (
        'Product',
        'Product'[Product Name],
        "Sales", 'Internet Sales'[Internet Total Sales]
    ),
    ISBLANK ( 'Internet Sales'[Internet Total Sales] ) = TRUE
)

And if you want the products with sale you can switch “TRUE” with “FALSE”

EVALUATE
FILTER (
    SUMMARIZE (
        'Product',
        'Product'[Product Name],
        "Sales", 'Internet Sales'[Internet Total Sales]
    ),
    ISBLANK ( 'Internet Sales'[Internet Total Sales] ) = FALSE
)

How to use “CrossJoin” in DAX

Posted on by • Posted in Basic script • Tagged , • 1 Comment

The “CrossJoin” function returns the Cartesian product of all rows from all tables in the arguments. In my sample below I want to have sales per year per city.

EVALUATE
ADDCOLUMNS (
SUMMARIZE (
CROSSJOIN (
VALUES ( 'Date'[Calendar Year] ),
VALUES ( Geography[City] )
),
'Date'[Calendar Year],
Geography[City]
),
"Sum of Sales", CALCULATE ( SUM ( 'Internet Sales'[Sales Amount] ) )
)

And the result looks like this

CrossJoin1

But as you can see I get a lot of empty rows. To remove these empty rows I add a filter to my query. I also moved my calculation outside the main query to make it more readable.

DEFINE
MEASURE 'Internet Sales'[Sales] =
SUM ( 'Internet Sales'[Sales Amount] )
EVALUATE
ADDCOLUMNS (
FILTER (
SUMMARIZE (
CROSSJOIN (
VALUES ( 'Date'[Calendar Year] ),
VALUES ( Geography[City] )
),
'Date'[Calendar Year],
Geography[City]
),
'Internet Sales'[Sales] <> 0
),
"Sum of Sales", 'Internet Sales'[Sales]
)

CrossJoin 1

How to use “RANKX”

Posted on by • Posted in Basic script • Tagged , , , • Leave a comment

If you want to rank your result in DAX you can use the RANKX function.

Below is a sample

DEFINE
MEASURE 'Internet Sales'[Rank] =
RANKX (
ALL ( 'Product'[Product Name] ),
SUMX (
RELATEDTABLE ( 'Internet Sales' ),
'Internet Sales'[Internet Total Sales]
)
)
EVALUATE
FILTER (
ADDCOLUMNS (
VALUES ( 'Product'[Product Name] ),
"Product Rank", 'Internet Sales'[Rank],
"Sales amount", 'Internet Sales'[Internet Total Sales]
),
[Sales amount] <> 0
)
ORDER BY 'Internet Sales'[Rank]

Please note the filter that removes the empty rows.

Here is the resultset

Rank

Get Top N results in DAX

Posted on by • Posted in Basic script • Tagged , , , • Leave a comment

To get Top N results in DAX we can use the TOPN function.

EVALUATE
TOPN (
    10,
    SUMMARIZE (
        'Internet Sales',
        'Product'[Product Name],
        "Sales Amount", 'Internet Sales'[Internet Total Sales]
    ),
    'Internet Sales'[Internet Total Sales]
)
ORDER BY 'Internet Sales'[Internet Total Sales] DESC

A similar MDX query might look like this

SELECT TOPCOUNT(
	[Product].[Product Name].[Product Name],
	10, 
	[Measures].[Internet Total Sales]) 
ON 1,
[Measures].[Internet Total Sales] ON 0
FROM [Internet Sales]

If you want the bottom N results you can write a DAX like this

EVALUATE
TOPN (
    10,
    SUMMARIZE (
        'Internet Sales',
        'Product'[Product Name],
        "Sales Amount", 'Internet Sales'[Internet Total Sales]
    ),
    'Internet Sales'[Internet Total Sales],
    1
)
ORDER BY 'Internet Sales'[Internet Total Sales] ASC

In MDX we replace the TOPCOUNT with the BOTTOMCOUNT. And then we add a filter to avoid empty rows

SELECT BOTTOMCOUNT(
	FILTER(
		[Product].[Product Name].[Product Name], 
		NOT ISEMPTY([Measures].[Internet Total Sales])
		),
	10, 
	[Measures].[Internet Total Sales])
ON 1,
[Measures].[Internet Total Sales] ON 0
FROM [Internet Sales]

Paging in DAX using “Start At”

Posted on by • Posted in Basic script • Tagged • Leave a comment

If you want to use paging in your DAX query you can do this by using the “Start At” parameter in “Summarize”.

Here is a sample on how this can be done:

EVALUATE
SUMMARIZE (
'Date',
'Date'[Month Name],
'Date'[Calendar Year],
"Sales", 'Internet Sales'[Internet Total Sales]
)
ORDER BY 'Date'[Calendar Year]
START AT 2007

The code above will give sales per year per month starting at the year of 2007. Please note that this also returns empty rows. In the next sample we remove the blank rows and extend our “Start At” to also handle month names.

EVALUATE
FILTER (
SUMMARIZE (
'Date',
'Date'[Month Name],
'Date'[Calendar Year],
"Sales", 'Internet Sales'[Internet Total Sales]
),
'Internet Sales'[Internet Total Sales] <> 0
)
ORDER BY 'Date'[Calendar Year], 'Date'[Month Name]
START AT 2007,
"July"

This query will give sales per year per month for year later than 2007 and month name later than july. Blank rows will also be removed using the filter function.

Creating an simple if test in DAX

Posted on by • Posted in Basic script • Tagged , , , • Leave a comment

Here is a sample on how to create an if test in DAX. It is really easy 🙂 In the sample below we test the sales per month for 2007 versus the sales per month for 2006. If the sales are higher we print “Higher”. If it was lower we print “Lower”.

DEFINE
    MEASURE 'Internet Sales'[Previous Sales per month] =
        CALCULATE (
            'Internet Sales'[Internet Total Sales],
            SAMEPERIODLASTYEAR ( 'Date'[Date] )
        )
    MEASURE 'Internet Sales'[Sales per month] =
        CALCULATE (
            'Internet Sales'[Internet Total Sales],
            'Date'[Month]
        )
    MEASURE 'Internet Sales'[MyTest] =
        IF (
            'Internet Sales'[Sales per month]
                >= 'Internet Sales'[Previous Sales per month],
            "Higher",
            "Lower"
        )
EVALUATE
FILTER (
    SUMMARIZE (
        'Date',
        'Date'[Month],
        'Date'[Calendar Year],
        "Previous Sales per month", FORMAT (
            'Internet Sales'[Previous Sales per month],
            "Currency"
        ),
        "Sales Sales per month", FORMAT ( 'Internet Sales'[Sales per month], "Currency" ),
        "Change", 'Internet Sales'[MyTest]
    ),
    'Date'[Calendar Year] = 2007
)