Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
lukiz84
Memorable Member
Memorable Member

DAX shows all unrelated rows, but calculated column correct

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

(SSAS 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

 

absences.PNGabsenceClaim.PNGrelationship.PNGrelatedOK.PNGrelatednotOK.PNG

1 ACCEPTED SOLUTION

Hi @lukiz84 

 

Friend, one has to have a solid grounding in how filtering in DAX works to be able to explain the behaviour you experience (which is correct, by the way) and then recognize the simple fact that one should never drag columns from tables that are not dimensions onto the canvas (unless one is debugging). Slicing and dicing should only ever be done by dimensions and this simple rule can't be stressed enough. Fact table MUST always be hidden from view. Only measures can reside unhidden in the fact tables but it's better to completely hide the facts and have a separate measure-only table holding all the measures in the model. This way you won't shoot yourself in the foot and will not be taken by surprise.

 

Please read this to understand how to correctly model data for Power BI (and many other BI tools for that matter). I would also kindly recommend that you read "The Definitive Guide to DAX" if you want to know how DAX really works and be able to explain the behaviour you see. But, the short of it is that you should stick to the rules as outlined above and you'll be OK.

View solution in original post

6 REPLIES 6
lukiz84
Memorable Member
Memorable Member

I thought got it... please see the edit below:

 

The listed AbsenceGroups (left picture) exist in all of my AbsenceCodes (right picture). And the fact table has 01,02,03,11,G-100,G-120. So if I just drag AbsenceCode, the pivot checks for all possible AbsenceCodes which "have" those AbsenceGroups. That seems to explain it and actually makes sense.

 

Please correct me if I'm wrong.

 

EDIT: Just for testing purposes I removed the entries with AbsenceGroup '01' which is the only one where AbsenceCode can be 'KA', but dragging AbsenceCode from AbsenceClaim still lists AbsenceCode 'KA' which makes my thought explanation obsolete... (

 

BR

Lukas

@lukiz84 

 

I could do with some sample data where you could easily demonstrate the vex of yours :)))

Hi, thanks again. When preparing your sample I just found out that my measure was pointing to AbsenceClaim, which should be Absences...

 

So, thats the first mistake I made... 

 

Nonetheless, it just moves the problem. Now, if i drag the Column AbsenceCode from AbsenceClaim the expected result is there, but dragging the AbsenceCode column from Absences (from where the measure is calculated) it lists all AbsenceCodes again...

 

very confusing. I've attached a sample excel file with some rows loaded into PowerPivot.

 

Sample file on filebin 

 

BR

Lukas

Hi @lukiz84 

 

Friend, one has to have a solid grounding in how filtering in DAX works to be able to explain the behaviour you experience (which is correct, by the way) and then recognize the simple fact that one should never drag columns from tables that are not dimensions onto the canvas (unless one is debugging). Slicing and dicing should only ever be done by dimensions and this simple rule can't be stressed enough. Fact table MUST always be hidden from view. Only measures can reside unhidden in the fact tables but it's better to completely hide the facts and have a separate measure-only table holding all the measures in the model. This way you won't shoot yourself in the foot and will not be taken by surprise.

 

Please read this to understand how to correctly model data for Power BI (and many other BI tools for that matter). I would also kindly recommend that you read "The Definitive Guide to DAX" if you want to know how DAX really works and be able to explain the behaviour you see. But, the short of it is that you should stick to the rules as outlined above and you'll be OK.

lukiz84
Memorable Member
Memorable Member

Hi daxer-almighty,

 

thanks for your swift response but I don't get it 100%.

 

I don't understand, why dragging AbsenceGroup from the lookup-table "AbsenceClaim" returns _my expected_ result. It shouldn't make a difference and also list all possible combinations according to your post. But it just lists the AbsenceGroups which are also present for the specific reporting month in my fact-table "Absences". I have to say, I'm a little confused. Why does one column differ from another column?!

 

The filter for the lookup-table is defined by the RowID, at least that's what i thought?

(Especially given the fact that using a calculated column in Absences which just is RELATED(Absences[AbsenceCode]) returns my expected result?)

 

Please see the screenshots, shouldn't there also be ALL possible AbsenceGroups (which are way more then the Pivot lists) if I read your answer correctly?

 

Thanks so much for your help.

 

correct.PNGincorrect.PNG

daxer-almighty
Solution Sage
Solution Sage

@lukiz84 

 

If a measure returns a non-blank value for a combination of attributes from 2 different tables, this combination will be displayed. The auto-exist feature that removes non-existent combinations of attributes only works on attributes coming from the same table. You can easily observe this when you create a measure like [Measure] = Today() and put attributes from different tables into a table/matrix, then drop the measure onto the canvas. All combinations will be displayed regardless of what's in a fact table.

 

Hope this helps.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors