cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jasmine_D
Regular Visitor

Changing granularity from event to daily

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:

 

Jasmine_D_0-1617890025111.png

 

To a fact table format like this - with one row per day:

 

Jasmine_D_1-1617890882285.png

 

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

 

1 ACCEPTED 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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

11 REPLIES 11
Jihwan_Kim
Community Champion
Community Champion

Hi, @Jasmine_D 

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.

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.

 

 

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

 

 

@Jihwan_Kim 

 

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, @Jasmine_D 

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.

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

@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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
amitchandak
Super User IV
Super User IV

@Jasmine_D , 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

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Hi @amitchandak 

 

Thank you for your effort, I'm trying to understand the measures you've made!

 

Jasmin

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors