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
Anonymous
Not applicable

Numbers not Accurate Because of Relationship Issue

Hi,

 

I have these two tables with Actual Value & Goal Value. I have two look up table Note writers & Date.

The 1st Image shows the relationship I created & I can not do the date fields both way. Not sure how to create the relationships so the end Product will look like the second Image.

Image 1 & 2 :

Productivity 1.PNGProductivity 2.PNG

 

 

 

 

 

For now because there is no Date relationship between these 2 data table, the Goal numbers are not showing correctly.

Please help me to fix this.

Will really appreciate any help.

 

Thanks,

panda2018

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Sorry about that, got busy with my actual work 🙂

 

But had a few minutes so here's what I got.  ( I attached the pbix below, so please look at the file and the data in Power Query)

 

So in Power Query (all the steps are on the left under Applied Steps, so you can step through):

  • Import your two tables (hey, easy so far..)
  • In the Actuals Table, add a column combining YearMonth
  • Do the same in Goals table
  • I created a quick calendar table in there, just has some basic things, but needed

 

Then load that into the desktop,  So we have three tables now; Actuals, Goals, and Calendar.  

  • In the Calendar table need select "Mark as Date Table" Modeling-->Calendars in Data view (maybe not needed, but always good)
  • But need to sort "Month" by "MonthNum".  Select Month then Modeling-->Sort-->Sort by Column-->MonthNum
    • Otherwise will be in alphabetically order
  • We can then related that Calendar table to the Actuals table.  1:M and no problems
  • Can try to do the same to the Goals table, but nope, since goals only has YearMonth that repeat and the calendar table is at day granularity, cannot relate them.  Hmm..
    • We can create a bridge table that will connect both calendar and Goals. Easiest way is to create new table :
    • YearMonthList = VALUES('Calendar'[YearMonth])
    • Then we can relate taht to both the Goals and Calendar Tables ( 1:M) and all works.  

But then we have an issue of the Note Writer.  If we use Note Writer from the actuals table, it has no way to "talk" to the Goals table.  What do we do?  Another bridge table.  This time just the values of the note writers:

Note Writers = VALUES(Actuals[Note Writer] )
  • Relate that table to both the Goals and Actuals tables, resulting in this data model:

Data Model.png

 

Now we can write a few measures:

Total Service Hours, that's the easy one:

Total Service Minutes = SUM ( Actuals[Service Minutes] )

Total Goal, that's a little harder:

Total Goal = 
IF
/*this just ensures we dont see values at the individual date level
  since our goal data is at the monthly level*/
    NOT ( ISFILTERED( 'Calendar'[Date])),
    
 /*We can use the Calendar table as a filter for our goals
 its the concept of expanded tables*/
    CALCULATE(SUM ( Goals[Goal] ),'Calendar')
)

expanded tables is quite the topic and well out of the scope of this post. But there are many resources out there. In fact wrote a small post about it here:

https://community.powerbi.com/t5/Desktop/Issues-with-filter-propagation/m-p/565297#M266774

 

Anyway, our final table looks like this:

Final Table.png

Just be sure to use Note Writer from our bridge table, and not from either the Actuals or Goals tables.  Take a look and see what you think.  Couldnt spend a whole host of time on it, but the basic premise should be there.

 

PBIX File:

https://1drv.ms/u/s!AoQIGRpzoxRH8kdunea9RFluJQh4

 

View solution in original post

11 REPLIES 11
AkhilAshok
Solution Sage
Solution Sage

First of all, not really sure why you need Bi-directional relationship. I would first make the Cross Filter direction Single in all your relationships. Afterwards, you can add a relationship between Goal[Month] and Date[Date] (assuming Goal[Month] is in Date format). This should solve all your issues.

Anonymous
Not applicable

It doesn't work for the Goals though.

The goal table doesn't filter by Date (Month) ..It shows all the data.

 

Thanks @AkhilAshok

Make sure to use Month from the Date table in your visuals. For sorting Month column in Date table, just add a Month number column, and sort Month Name based on Month Number.

Anonymous
Not applicable

That really works!! Thank you @AkhilAshok

One question, Which date fiels I should use to show the Months only & How to sort it to show by month Order?

 

Thank you so much!! @AkhilAshok

Anonymous
Not applicable

can you share any data, even if fictitious? 

Anonymous
Not applicable

@Anonymous...I hope you got my data.

 

Because there is no date cross filter between Goal & date, I have put twotables side by side .

It works just to show the correct data. But When I do Month filter It doesn't filter the Goal table data. See the Image..

When I select December, it doesn't filter the Goal table data..

Productivity 3.PNG

Will really appreciate any help!! 

 

 

Anonymous
Not applicable

Sorry about that, got busy with my actual work 🙂

 

But had a few minutes so here's what I got.  ( I attached the pbix below, so please look at the file and the data in Power Query)

 

So in Power Query (all the steps are on the left under Applied Steps, so you can step through):

  • Import your two tables (hey, easy so far..)
  • In the Actuals Table, add a column combining YearMonth
  • Do the same in Goals table
  • I created a quick calendar table in there, just has some basic things, but needed

 

Then load that into the desktop,  So we have three tables now; Actuals, Goals, and Calendar.  

  • In the Calendar table need select "Mark as Date Table" Modeling-->Calendars in Data view (maybe not needed, but always good)
  • But need to sort "Month" by "MonthNum".  Select Month then Modeling-->Sort-->Sort by Column-->MonthNum
    • Otherwise will be in alphabetically order
  • We can then related that Calendar table to the Actuals table.  1:M and no problems
  • Can try to do the same to the Goals table, but nope, since goals only has YearMonth that repeat and the calendar table is at day granularity, cannot relate them.  Hmm..
    • We can create a bridge table that will connect both calendar and Goals. Easiest way is to create new table :
    • YearMonthList = VALUES('Calendar'[YearMonth])
    • Then we can relate taht to both the Goals and Calendar Tables ( 1:M) and all works.  

But then we have an issue of the Note Writer.  If we use Note Writer from the actuals table, it has no way to "talk" to the Goals table.  What do we do?  Another bridge table.  This time just the values of the note writers:

Note Writers = VALUES(Actuals[Note Writer] )
  • Relate that table to both the Goals and Actuals tables, resulting in this data model:

Data Model.png

 

Now we can write a few measures:

Total Service Hours, that's the easy one:

Total Service Minutes = SUM ( Actuals[Service Minutes] )

Total Goal, that's a little harder:

Total Goal = 
IF
/*this just ensures we dont see values at the individual date level
  since our goal data is at the monthly level*/
    NOT ( ISFILTERED( 'Calendar'[Date])),
    
 /*We can use the Calendar table as a filter for our goals
 its the concept of expanded tables*/
    CALCULATE(SUM ( Goals[Goal] ),'Calendar')
)

expanded tables is quite the topic and well out of the scope of this post. But there are many resources out there. In fact wrote a small post about it here:

https://community.powerbi.com/t5/Desktop/Issues-with-filter-propagation/m-p/565297#M266774

 

Anyway, our final table looks like this:

Final Table.png

Just be sure to use Note Writer from our bridge table, and not from either the Actuals or Goals tables.  Take a look and see what you think.  Couldnt spend a whole host of time on it, but the basic premise should be there.

 

PBIX File:

https://1drv.ms/u/s!AoQIGRpzoxRH8kdunea9RFluJQh4

 

Anonymous
Not applicable

This is just ..so much help!! 🙂 

Thank you so much for explaining so well!!

 

panda2018

 

Anonymous
Not applicable

Glad it is working or at least gives you a good start to build in the future.  The one thing I would change for sure is to build the tables in Power Query vs. with DAX.  But depending on the size of the data it may or may not have an impact.  

Anonymous
Not applicable

Is this Okay ?

1st Table has Actual provided Service, Second one has Goals.

Fake Data1.PNG

 

Fake Data 2.PNG

 

 

Thanks @Anonymous

Anonymous
Not applicable

How do I share Excel file?

Thanks @Anonymous

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.