DAX shows all unrelated rows, but calculated column correct

luki zw 21 Reputation points
2021-04-30T04:36:41.903+00:00

Hi, I have a strange behaviour in my model which makes me think I just don't understand relationships right.

(SSAT Tabular, Comp level 1500, Client Excel Pivot table)

Following Scenario (hard to explain):

  • I have two tables, AbsenceClaim und Absences which are related by RowID
  • The RowID-Column is made of AbsenceCode and AbsenceGroup in both tables
  • RowID is unique in AbsenceClaim, so it's 1 (AbsenceClaim) : N (Absences) - each absence has a claim (see first 3 attachments)
  • I have a measure SickDays which just sums up the days column in Absences
  • When i drag the AbsenceCode field from the ABSENCES Table, everything is correct
  • When i add a calculatedColumn in the ABSENCES Table (just for testing purposes) it correctly retrieves the AbsenceClaim-Column (see "FromRelatedTable"-column)
  • Excel lists the correct column (Absences[AbsenceCode] is the same as the calculated column) (Attachment 4)
  • BUT now the strange thing (Attachment 5): When i drag the AbsenceCode field from the related AbsenceClaim table, it lists all possible AbsenceCodes
  • BUT if i drag any other item from AbsenceClaim (e.g. AbsenceGroup) everything works as expected, too.

I've tried single direction and both directions for the relationship, it doesn't make a difference?

What am I getting wrong?!

Thanks in advance

Lukas

Table Absences:
92710-absences.png

Table AbsenceClaims:
92841-absenceclaim.png

Relationship:
92757-relationship.png

OK with calculated column (=RELATED(AbsenceClaim[AbsenceCode]))
92758-relatedok.png

?????:
92680-relatednotok.png

SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,006 questions
No comments
{count} votes

Accepted answer
  1. Alexei Stoyanovsky 3,321 Reputation points
    2021-05-01T08:54:47.32+00:00

    You seem to be forgetting that the relationship is based on RowID, and apparently there're several AbsenceGroups for a single AbsenceCode.


0 additional answers

Sort by: Most helpful