Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Wonderful Power BI'ers,
I have "general problem" - I'm changing granularity on a lot of fact-tables from event to daily. Meaning I'm going from:
To a fact table format like this - with one row per day:
This fixes a lot of my problems - but introduces new ones. I need to sums on the fact, in this case FullTimeRatio - in different card/table/matrix visualisations and add information from dimension-tables. This works fine before the change of granularity - but not after. I have a feeling there is a clever solution creating a measure - I've been fiddling with some complex solutions that doesn't entirely work.
How do I go about summing the facts in daily granularity - but getting result as it was from an event granularity? I'd be so grateful for some enlightenment.
Best BI-wishes,
Jasmin
Solved! Go to Solution.
Hi,
in the DailyGrain table, create another column in the query editor in which you can divide the FullTimeratio figure by the number of days+1. This then column can simple be added.
Hi, @Anonymous
I am not sure about how your other dimension tables look like, and how your data model look like, but if I may suggest.... I prefer the previous version with two date columns in one fact table.
In this case, you can have one dim-date-table, and create two inactive relationship with the fact table.
You can always turn on the relationship by creating a relevant DAX measures.
By doing this, you can do the below analysis and more.
- how much is the total full time ratio during the selected period?
- How many people worked during the selected period?
and so forth.
If it is OK with you, please share your sample pbix file, then I can try to come up with answering your current problems in an efficient way.
Thank you.
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi Jihwan,
But.. Keeping the original model with two dates - is it then possible to create a date-slicer and how?
Best,
Jasmin
Hi,
Yes, it is possible.
In this case, you can have one dim-date-table, and create two inactive relationship with the fact table.
You can always turn on the relationship by creating a relevant DAX measures.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi Again,
I've created a .pbix that illustrates the problem:
Pbix - different grains Download link
At the event grain - numbers are right, but I don't know how to slice by date. I hope this can illustrate better than my words.
Regards,
Jasmin
By looking at my example pbix - do you think you could illustrate the concept of inactive relationships and DAX measure. For example for having both a date slicer and the "event" grain while being able to correct sum Fulltimeration by event.
Best,
Jasmin
Hi, @Anonymous
Please correct me if I wrongly created the report.
please check the link down below.
Before you go further, please check your original table, which has two date columns, whether the start date and end date are correctly inputted. I could not know what is the correct data, so I just assumed based on your other fact table and amend it a bit. The reason why I amend is because the start date was showing later than the end date.
https://www.dropbox.com/s/3gocyy7ihwj6cod/differentgraindifferentpain%202.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi,
in the DailyGrain table, create another column in the query editor in which you can divide the FullTimeratio figure by the number of days+1. This then column can simple be added.
@Ashish_Mathur This works! Thank you so much - i appreciate it. I like things that work. I have this feeling that it is a little strange, needing to add new columns with the valued divided into small pieces. But it works 🙂 Thank you @Ashish_Mathur
You are welcome. If my previous reply helped, please mark that as Answer.
@Anonymous , if it something we can add, one option was to divide it when we split the data
Check the file after signature for one solution
and this blog for another solution
Hi @amitchandak
Thank you for your effort, I'm trying to understand the measures you've made!
Jasmin
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |