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

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
Super User
Super User

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.


Go to My LinkedIn Page


Anonymous
Not applicable

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.


Go to My LinkedIn Page


Anonymous
Not applicable

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

 

 

Anonymous
Not applicable

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


Go to My LinkedIn Page


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

@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
Super User

@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

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-or-end-date/ba-p/1503785

Anonymous
Not applicable

Hi @amitchandak 

 

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

 

Jasmin

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.