Forum Discussion

NickHoffmans's avatar
NickHoffmans
Frequent Visitor
4 years ago
Solved

Measure with cross filter

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?

 

 

COUNT
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

4 Replies

  • VahidDM's avatar
    VahidDM
    Super User
    4 years ago

    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/

     

     

     

     

     

  • bcdobbs's avatar
    bcdobbs
    Community Champion
    4 years ago

    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's avatar
      NickHoffmans
      Frequent Visitor
      4 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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