Use you inactive relations with “USERELATIONSHIP” in DAX

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

Advertisements

Count your top selling products with DAX

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)

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()

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

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

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”

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