topic Re: DAX: Unwanted cartesian product lines? in DAX Commands and Tips

DAX: Unwanted cartesian product lines?

Anonymous — Wed, 11 Jan 2023 14:16:26 GMT

Please help to calculate/understand properly lastDate and rankDate measures for following simplified example (download) :

Desired result:

Reality (incorrect subtypes):

Why relationship is broken?

How to avoid this cartesian product lines?

My Measure (I commented workaround, because it's kind of postfilter, not prefilter):

rnkDate = 
VAR t =
    CALCULATETABLE(
        VALUES(tstTable[Date]),
        REMOVEFILTERS(tstTable[Date])
    )
RETURN 
    //IF( MAX(tstTable[Amount])<>BLANK(), // WORKAROUND To hide unwantedd rows
        RANKX(
            t,
            LASTDATE(tstTable[Date])
        )
    //)

P.S. Mess happens only if I use fields from dimensional table dimType[Type] (within one table everything is Ok):

Re: DAX: Unwanted cartesian product lines?

tamerj1 — Wed, 11 Jan 2023 13:33:31 GMT

Hi Anonymous
Please refer to attached sample file with the solution

lastDate = CALCULATE ( LASTDATE ( tstTable[Date] ), REMOVEFILTERS ( tstTable[Date] ), CROSSFILTER ( tstTable[subType], dimType[subType], BOTH ) )

rnkDate = VAR t = CALCULATETABLE ( VALUES ( tstTable[Date] ), REMOVEFILTERS ( tstTable[Date] ) ) VAR Result = RANKX( t, CALCULATE ( MAX ( tstTable[Date] ) ) ) RETURN IF ( NOT ISEMPTY ( tstTable ), Result )

Re: DAX: Unwanted cartesian product lines?

Anonymous — Thu, 12 Jan 2023 08:55:14 GMT

tamerj1 , thank you for your feedback.

Regarding lastDate, as far as I understand crossfilter is not necessary. And here is more important question for me regarding this measure
Regarding rnkDate, the question is why it returns cartesian product for subTypes if we do NOT use if statement? Is it normal?
NB! it's NOT about empty values, it's about CARTESIAN product
A bit of topic: how do you attach .pbix direcctly in post? When I try drag&drop

Re: DAX: Unwanted cartesian product lines?

tamerj1 — Thu, 12 Jan 2023 11:11:54 GMT

Anonymous
1. Yes you are right.

2. The issue is related to the filter context. One column of the fact table (the [Date] column) is used in the matrix therefore, it's part of the filter context. In this case the filter context created by the engine is the CROSSJOIN between the SUMMARIZE ( dimType, dimType[Type], dimType[subType] ) and DISTINCT ( tstTable[Date] ) which generates all the possible combinations between the two tables.

When placing a normal aggregation measure in the matrix the engine checks first if the value is blank then it hides it. With RANKX things are different and the reason is that even the balnk value will have a rank (because DAX considers blank as zero in calculations) and the rank cannot be blank rather it will be the samllest or the largest rank value depending on the sort oreder of RANKX.

3. You can upload to DropBox or OneDrive and share the download link.