topic Measure with cross filter in DAX Commands and Tips

Measure with cross filter

NickHoffmans — Sat, 18 Dec 2021 10:48:25 GMT

Hello,

I have a question on creating a measure, taking into account multiple tables.

In one table ("Date-table") I have a set of data, with a row for each date between 2015 and 2025. This table contains a column with an logical indicator whether the date in row is a "Workday" or not.
In the other table ("Asset-table") I have a set of data on fixed assets. This table contains two interesting columns: Purchase date and Selling date
Now, I would like to have the number of "Workdays" per Fixed Asset (in order to calculate the occupancy rate afterwards). I now simply count:

Number of Workdays available = COUNTAX(FILTER('Date', Date[IsWorkDay]=TRUE()),TRUE())

This works well, but it does not take into account that the asset can be bought or sold during the selected date range.

So, I am now looking for a solution (either in DAX or PowerQuery) in which I can combine these events.

I have now created a Crossjoin-table from both tables. I think I now have the complete set needed to ma

So I'm looking for something like this:

Number of Workdays available = COUNTAX(FILTER('Date', if(IsWorkday<= DateSold and IsWorkday >= DatePurchased),Date[IsWorkDay]=TRUE()),TRUE())

Do you have any suggestion?

Re: Measure with cross filter

VahidDM — Sat, 18 Dec 2021 12:52:57 GMT

Hi NickHoffmans

Try this:

Number of Workdays available = COUNTROWS( FILTER( 'Date', 'Date'[Date] <= DateSold && 'Date'[Date] >= DatePurchased && 'Date'[IsWorkDay] = TRUE() ) )

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

Re: Measure with cross filter

bcdobbs — Sat, 18 Dec 2021 22:39:56 GMT

You could consider changing the structure of your data model to make the DAX easier and optimise the whole thing.

You want to end up with a factless fact table with columns:

Date, AssetId, OtherIds

Each asset then has a row for every day it is owned. Purchase date is the earliest row. Sale date is the last row.

When I came from an excel background this seemed alien as you end up with lots more rows but the engine can cope with it and if you keep it so you just store integer Ids from

dimension tables and a date the compression keeps things small and very fast.

Lots of ways to achieve such a table but you could cross join your asset table with your date table in power query and then Filter out rows outside of date range. (https://docs.microsoft.com/en-us/power-query/cross-join)

Re: Measure with cross filter

NickHoffmans — Mon, 20 Dec 2021 13:09:00 GMT

Thanks. What is the difference between the crossjoin in DAX and the crossjoin in PowerQuery?

Re: Measure with cross filter

Anonymous — Thu, 23 Dec 2021 02:14:20 GMT

Hi NickHoffmans

CROSSJOIN DAX : Returns a table that contains the Cartesian product of all rows from all tables in the arguments. The columns in the new table are all the columns in all the argument tables.

Cross join Query : A cross join is a type of join that returns the Cartesian product of rows from the tables in the join. In other words, it combines each row from the first table with each row from the second table.

The difference is , CROSSJOIN DAX create a new table to combine the two tables to one ,but Cross join Query is add a new column ,then expand the column .

You can learn more in the links below .

https://docs.microsoft.com/en-us/dax/crossjoin-function-dax

https://docs.microsoft.com/en-us/power-query/cross-join

I also create a sample for you , you can refer to it .

Best Regard

Community Support Team _ Ailsa Tao