Forum Discussion
Helper IIDAX 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 data. For some reason, although each account has specific dates associated it, Power BI is matching all the dates with all the accounts and calculating on that. When what I want it to do is only calculate on accounts that have the corresponding dates.
New_Date_Indicator = IF(SELECTEDVALUE('Current'[Statement Date])=SELECTEDVALUE(Previous[Previous Statement Date]),"0","1")
Current visual looks like this, but it is wrong, as it is not matching account with date properly.
I've established a 1 to 1 relationship joining on Account.
8 Replies
- amitchandak
Super User
matsahiro , You need to have a common account table and join these two tables with that. and analyze with the account numbers from the common table.
You can create a measure like
New_Date_Indicator = IF(max('Current'[Statement Date])=max(Previous[Previous Statement Date]),"0","1")
matsahiroamitchandak I went ahead and created the common account table and added indexes to the other two tables. I joined on the indexes with ID in account table. Here's what the relationship looks like.
The relationships looks to be right until I add in the indicator.
- matsahiro
- jameszhang0805
Resolver IV
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
I ended up merging the two data tables on account name in query editor. Thank you for your help!
- jameszhang0805
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
