Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 :
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
Solved! Go to Solution.
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):
Then load that into the desktop, So we have three tables now; Actuals, Goals, and Calendar.
YearMonthList = VALUES('Calendar'[YearMonth])
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] )
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:
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
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.
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.
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
can you share any data, even if fictitious?
@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..
Will really appreciate any help!!
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):
Then load that into the desktop, So we have three tables now; Actuals, Goals, and Calendar.
YearMonthList = VALUES('Calendar'[YearMonth])
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] )
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:
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
This is just ..so much help!! 🙂
Thank you so much for explaining so well!!
panda2018
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.
Is this Okay ?
1st Table has Actual provided Service, Second one has Goals.
Thanks @Anonymous
How do I share Excel file?
Thanks @Anonymous
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |