Data model and Measures

Power BI Data Model
1

Hey All,
I am currently on this project, Please i need help with the large table(Extract).
I attached additional information for the measures and goals i intend to achieve.
I will appreciate any help i can get.
Thanks in advance
@BrianJ
Extract.xlsx (7.3 MB) Goals & Measures.docx (15.2 KB)

2

@Egbejiogu,

I would like to be able to assist you with this, but as the request currently stands it is miles outside the bounds of an appropriate forum request. What you first need to do is to make your best attempt at building that 80 column flat file into a star schema dimensional data model that will support the analyses you want to do. Then attempt to create the measures and analyses you desire. If at that point you run into problems, then post your PBIX file with an explanation of the specific problems you’re having, and a mockup of the results you want to achieve.

If you are unsure how to transform that flat file into an appropriate dimensional model, take or review the Data Transformations and Data Modeling course in the portal, where Sam walks through exactly how to do this.

I hope this is helpful.

– Brian

1 Like
3

Hey @BrianJ
Thanks so much
sorry i didn’t attach my PBI file with my ist draft.
I have the tables and schema created but trying to figure out how to achieve the goals/measures…
Here is the PBIX FILE …Let me know if you need additional information.
JAM.pbix (4.0 MB)

4

@Egbejiogu,

This is a little better, but the request is still in the realm of full-blown consulting support rather than forum support. Without relationships specified, this isn’t yet a data model. Also, it has no Date table, which is absolutely essential for time intelligence calculations, the category that all of your desired measures fall into. Also, please keep in mind that while you’ve been working with this large dataset extensively and understand the field names, the assocated business processes and other important aspects of this data, no one providing support on the forum will have this knowledge. That’s why it’s so important to be as specific as possible in your questions, provide mockups of the results you want to achieve and take an initial run at the answer yourself, so that we have a strawman to respond to that doesn’t require in-depth knowledge of your data and business.

That being said, I think the “Events in Progreass” DAX Patterns series developed by Enterprise DNA Expert @Greg will be extremely helpful to you in developing the desired measures.

Still a lot more work to be done on your end first, but once you get to that point if you’re having problems, give a shout.

Thanks.

  • Brian
2 Likes
5

Ok @BrianJ
I will work on everything you just mentioned now and will let you know the next process.
Thanks

1 Like
6

Hi @Egbejiogu, did the response provided by @BrianJ help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

7

Hi @Egbejiogu, we’ve noticed that no response has been received from you since the 21st of March. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

8

Hey @EnterpriseDNA
I am working on the model requirements and measures like @BrianJ suggested, as soon as am done I will let him know where i need support.
Thanks

9

Hey @BrianJ
Now that i have the model and relationship ready, i created couple of measures thanks to your advice but Pls am trying to figure out the length of reports based on engagement letter date.
What will be the best DAX formula to use? datediff or dateinbetween?
How to i write the formular to get
1.under 180 days reports and
2. 181 - 365 days report.
Thanks in advance for all your support.

10

@Egbejiogu,

It’s hard to say with confidence without seeing your data model or PBIX, but generally speaking I tend to handle these sorts of problems by relying heavily on the offset fields in @Melissa’s awesome Extended Date Table.

If you’re not familiar with that approach, she did an excellent video on it a while back explaining how offsets can be used to solve a wide range of time intellgence problems.

I hope this is helpful.

  • Brian
1 Like
11

Hi @Egbejiogu, we’ve noticed that no response has been received from you since the 29th of March. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

12

Hi @Egbejiogu, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!