Forum Discussion

matsahiro's avatar
matsahiro
Icon for Helper II rankHelper II
5 years ago

DAX Calculating on Wrong Rows - Not Recognizing Join Relationship Properly

Hello, I'm trying to figure out why DAX isn't calculating properly on the right rows. I have this indicator made in DAX trying to show which accounts have different dates from previous and current da...
jameszhang0805's avatar
jameszhang0805
Icon for Resolver IV rankResolver IV
5 years ago

matsahiro  
If I understand correctly, you are trying to find accounts with the same current date as the previous date,
then to calculate some expressions based on the filtered results. 
So in your sample, Bob and Pat need to be screened out.
Virtual data source：

Result:

My solution is based on having a common account table and joining two other tables in a one-to-many relationship
Find out account which Current Date equal to Previous Date.pbix 

 

matsahiro's avatar
matsahiro
Icon for Helper II rankHelper II
5 years ago

I ended up merging the two data tables on account name in query editor. Thank you for your help!

 

  • jameszhang0805's avatar
    jameszhang0805
    Icon for Resolver IV rankResolver IV
    5 years ago

    Welcome, that's my pleasure. I know you have solved the problem by yourself.
    I just wanted to update the method of not having to build a common table and without relationships.

    CorrectAccoutAmt = 
VAR _CurrentTable =
    CALCULATETABLE (
        SUMMARIZE ( 'Current', 'Current'[Statement Date], 'Current'[Account] ),
        ALLSELECTED ()
    )
VAR _PreviousTable =
    CALCULATETABLE (
        SUMMARIZE ( 'Previous', Previous[Previous Date], 'Previous'[Account] ),
        ALLSELECTED ()
    )
VAR _CorrectAccount =
    INTERSECT ( _CurrentTable, _PreviousTable )
VAR _Result =
    CALCULATE (
        SUM ( 'Current'[Amount] ),
        KEEPFILTERS ( _CorrectAccount )
    )
RETURN
    _Result