Home
Training
Consulting
Articles & Blog
Videos
Books
Tools
About

Menu
Search
0
LOGIN

Home
Training
Consulting
Articles & Blog
Videos
Books
Tools
About

Accept cookies to show this video. Cookies Policy
Allow all cookies

CROSSJOIN – DAX Guide

CROSSJOIN: Returns a table that is a crossjoin of the specified tables.
https://dax.guide/crossjoin/

Aug 23, 2021

LinkedIn Twitter Facebook Email

Subscribe

Comments

You need to login in order to comment this video.

Login Or register

Subscribe

Notify of

Please login to comment

2 Comments

Inline Feedbacks

View all comments

Nikita Ilin

3 months ago

Hi, great guide! I hope you can help me with understanding cross join.
I have 3 tables: dimcustomer 1 – * factonlinesales * – 1 dimproduct
I have a question about how Power BI decides when to perform a cross-join. I know that blanks become 0 when compared to numbers, but I’ve noticed that cross-joins still happen even when using a constant or a simple function like sum(factonlinesales[unitprice]) + 0. It seems like Power BI might be applying a cross-join whenever any function returns data, but I believe there’s more to it.

Could you explain how Power BI determines whether to use a cross-join or not? It feels like the logic behind it is more complex than just “if data is returned, apply a cross-join.”

Here’s an example using IF:

EVALUATE
SUMMARIZECOLUMNS (
dimproduct[classname],
dimcustomer[gender],
“sum”, SUM ( factonlinesales[unitprice] ),
“if”,
IF (
SUM ( factonlinesales[unitprice] ) = 100,
“eq”,
IF ( SUM ( factonlinesales[unitprice] ) > 100, “greater”, “less” )
)
)

data:

dimcustomer

customerkey,firstname,gender
3,Aaron,
1,John,M
2,Lisa,F
4,David,M

dimproduct

classname,productkey
Econom,1
Deluxe,2
Regular,3

factonlinesales

customerkey,productkey,unitprice
3,2,200
1,1,100
2,1,100
3,3,300
2,2,200

Screenshot-2024-10-07-142223

Last edited 3 months ago by Nikita Ilin

0

StickClose

SQLBI

Alberto Ferrari

3 months ago

Reply to Nikita Ilin

It is a very complex discussion, not suitable for the space available here. However, you can find some good information here: Optimizing conditions involving blank values in DAX – SQLBI

0

All

12:47

Differences between ALL and ALLCROSSFILTERED

Jan 28, 2025
45:22

Optimizing incremental inventory calculation in DAX

Jan 14, 2025
05:16

What happened in 2024

Dec 30, 2024
25:11

Reusing formatting for Power BI reports

Dec 17, 2024
07:01

Detect and Fix Issues with Tabular Editor 3 and DAX Optimizer Integration

Dec 9, 2024
40:08

BLANK in date columns and DAX time intelligence functions

Dec 3, 2024
08:07

Real-World Use Case for DAX Optimizer: Real Estate

Dec 2, 2024
30:25

Compare DAX Optimizer vs ChatGPT vs Copilot for Power BI

Nov 25, 2024

Newsletter

By subscribing you are agreeing to our Privacy Policy.

246.0K subscribers

Get BI news and original content in your inbox every 2 weeks!
Newsletter archive

What is next

Mastering DAX – Feb 11
, Amsterdam
Mastering DAX – Feb 18
, Phoenix
Mastering DAX – Feb 24
, Dallas
Data Modeling for Power BI – Apr 14
, Amsterdam

YouTube Twitter LinkedIn Facebook Github RSS Feed

Marco Russo Alberto Ferrari Daniele Perilli

2004-2025 © SQLBI. All rights are reserved.
SQLBI is a trademark of SQLBI Corp.
Loader is a European legal entity.

Privacy Policy & Cookies Terms & Conditions Contact Us

You are going to send email to

Move Comment