Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

DAX: Unwanted cartesian product lines?

 

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):

DAX Measures
Help Request
Technical question

3 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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
    )

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    tamerj1 , thank you for your feedback.

    1. Regarding lastDate, as far as I understand crossfilter is not necessary. And here is more important question for me regarding this measure 
    2. 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
       

       

    3. A bit of topic: how do you attach .pbix direcctly in post? When I try drag&drop

       

    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      3 years ago

      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.