Measure with cross filter

[NickHoffmans]

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?

[v-yetao1-msft · Accepted solution]

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

[bcdobbs]

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)

[NickHoffmans]

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

[VahidDM]

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/