Forum Discussion
Measure with cross filter
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
Community ChampionYou 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)
Thanks. What is the difference between the crossjoin in DAX and the crossjoin in PowerQuery?