Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

2 fact table data prep help needed

Jump to Best Answer
  • 1.  2 fact table data prep help needed

    Bronze Contributor
    Posted Jul 05, 2018 08:54 AM
    In the past I've succeeded prepping date and revenue data from a single fact table to create a year-over-year cumulative "Burn-up [line] Chart" for charitable donation revenue. But now, I'm failing doing the same for distinct donor count because my date data is on the revenue fact table and the donor IDs I want to count is on a donor credit type table typically used as a fact table (some donations have recognition split among multiple donors associated with one contribution). The two tables can be linked on payment ID. At my novice level, I need the donor ID from one table adjacent to the date on the other table in order to do the summary table and then the special measure. Please help! #dataprep #PowerBI #Operations​​​

    ------------------------------
    Stephen Lambert
    Advancement Research Analyst
    Selinsgrove PA
    5703724420
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: 2 fact table data prep help needed

    Posted Jul 05, 2018 09:45 AM
    Providing the model would help us to see what's going on.

    But, by the description. I think you would need a date table. You could use something like this.

    Date =
    ADDCOLUMNS (
    CALENDAR (DATE(2018,1,1), TODAY()),
    "DateSK", FORMAT ( [Date], "YYYYMMDD" ),
    "Calendar Year", YEAR ( [Date] ),
    "Calendar Year Month", FORMAT ( [Date], "MM" ),
    "Day Of Month", FORMAT( DAY([Date]), "00" ),
    "YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
    "YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
    "MonthNameShort", FORMAT ( [Date], "mmm" ),
    "Month Name", FORMAT ( [Date], "mmmm" ),
    "Month Of Year", FORMAT( MONTH( [Date] ), "00" ),
    "Day Of Week", FORMAT( WEEKDAY ( [Date] ), "00" ),
    "Week Name", FORMAT ( [Date], "dddd" ),
    "DayOfWeekShort", FORMAT ( [Date], "ddd" ),
    "Calendar Quarter", FORMAT ( [Date], "Q" ),
    "Quarter", "Q" & FORMAT ( [Date], "Q" ),
    "YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ),
    "Calendar Year Quarter", FORMAT ( [Date], "YYYY" ) * 100 + FORMAT ( [Date], "Q" )
    )


    And map the both the fact tables to this date dimension.

    ------------------------------
    Yaswanth (Yash) Muthakapalle
    Greater Chicago Area, IL
    (302) 724-9274
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 3.  RE: 2 fact table data prep help needed

    Bronze Contributor
    Posted Jul 05, 2018 10:54 AM
    Forgive me. I'm not following.
    I have: Table 1 (ODS_CONTRIBUTION in image below) with revenue, payment ID, date, fiscal year, fiscal month, and month name (plus 3 dozen other fields).
    Table 2 (ODS_CONTRIB_DONOR_DESIG) with payment ID, and donor ID (no date but a dozen other fields).
    Payment ID is the one field the two tables have in common and this field is unique on Table 1 but multiple on Table 2.
    How do I get the date over on to Table 2 from Table 1?
    I believe this is the only way I'll be able to summarize the count of donor ID by fiscal month and year.
    Here's my annotated model:
    donor count modelThe data will ultimately feed this:
    Burn up chart of distinct donor count year over year

    ------------------------------
    Stephen Lambert
    Advancement Research Analyst
    Selinsgrove PA
    5703724420
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 4.  RE: 2 fact table data prep help needed
    Best Answer

    Posted Jul 05, 2018 02:06 PM
    Edited by Yaswanth (Yash) Muthakapalle Jul 06, 2018 12:08 PM
    I am thinking something like this. (Sorry for the messy diagram)


    On the Axis, use the data from "Dim" instead of Fact.

    ------------------------------
    Yaswanth (Yash) Muthakapalle
    Greater Chicago Area, IL
    (302) 724-9274
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 5.  RE: 2 fact table data prep help needed

    Top Contributor
    Posted Jul 06, 2018 09:32 AM
    ​Hello,
    @Yaswanth (Yash) Muthakapalle is correct that you should be leveraging a date dimension table for you time intelligence.  If the path remains 1 to many from
    Date (1 to Many) -> ODS_CONTRIBUTION (1 to Many) -> ODS_CONTRIB_DONOR_DESIG then you calculate measure such as DistCT = DISTINCTCOUNT('ODS_CONTRIB_DONOR_DESIG'[Condes_Donor]) should calculate correctly.

    Note that in your ODS_CONTRIBUTION you will need an actual date field.  Is it possible for you to get the Fiscal Date in addition to your Fiscal Year?  If so, I often will create a field called DateInteger = FORMAT ( [Fiscal Date], "YYYYMMDD" )  which I set equal to DateInteger = FORMAT ( [Date], "YYYYMMDD" ) in the Date Dimension.  @Yaswanth (Yash) Muthakapalle has it identified as ""DateSK", FORMAT ( [Date], "YYYYMMDD" )"
    ​​​​

    Brian

    ------------------------------
    Brian Connelly
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 6.  RE: 2 fact table data prep help needed

    Bronze Contributor
    Posted Jul 09, 2018 08:29 AM
    Brian,
    You're spot on in your first paragraph. That's precisely what I finally achieved late Friday afternoon and now I have a nice five-year burn-up line graph of donor counts.
    Your latter comment on dates is food for thought regarding how I approach date dim tables and date extraction in the future. I have a date field for contribution revenues coming in and I have a string field indicating fiscal year. From those I can extract fiscal month number and other things I need to make the visuals required of me.
    Thank you for contributing! It aids my learning a great deal.

    ------------------------------
    Stephen Lambert
    Advancement Research Analyst
    Selinsgrove PA
    5703724420
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 7.  RE: 2 fact table data prep help needed

    Top Contributor
    Posted Jul 09, 2018 09:44 AM
    ​I am glad to have help and please be sure to continue to ask questions. The PBI group is amazing.  IF you wouldn't mind, if my response was helpful and what you needed, please be sure to recommend the response.

    Brian

    ------------------------------
    Brian Connelly
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 8.  RE: 2 fact table data prep help needed

    Silver Contributor
    Posted Jul 13, 2018 11:11 AM
      |   view attached
    As you get into using Multiple FACT tables you may run into issues with Filter Context and measures not working as you would expect. I put together this tutorial for an internal workshop we had and it describes a number of ways of dealing them effectively.  This is probably the #1 Topic I coach people on that are new to PowerBI.



    ------------------------------
    Andrew Seward
    Senior Product Marketing Engineer
    8454758636
    ------------------------------

    Attachment(s)

    pptx
    Multiple Fact Tables.pptx   1.16MB 1 version
    Academy - Online Interactive Learning from Experts


  • 9.  RE: 2 fact table data prep help needed

    Bronze Contributor
    Posted Jul 13, 2018 04:30 PM
    The bridge table concept you mention is new to me in the Power BI context. It did indeed improve the accuracy of a calculation in one of my more frustrating models with two fact tables. Thank you!
    I'll have to spend additional time studying your slides because, in my novice state, I'm not able to fully follow the DAX statements.

    ------------------------------
    Stephen Lambert
    Advancement Research Analyst
    Selinsgrove PA
    5703724420
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 10.  RE: 2 fact table data prep help needed

    Bronze Contributor
    Posted Jul 19, 2018 07:46 AM
    @Andrew Seward - some of the screenshots in your ppt are cutting off information, like tips, and I don't find how to bring them back - would it be posisble to correct?​

    ------------------------------
    Domantas Gintauskas
    R&D Data Analyst
    GN Resound
    52829987
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 11.  RE: 2 fact table data prep help needed

    Silver Contributor
    Posted Jul 19, 2018 12:38 PM
      |   view attached
    @Domantas Gintauskas  Since there seems to be a fair amount of interest in this I went ahead and ​​​​recreated the PBIX workbook with dummy data so you can see a working data model.


    ------------------------------
    Andrew Seward
    Senior Product Marketing Engineer
    8454758636
    ------------------------------

    Attachment(s)

    Academy - Online Interactive Learning from Experts


  • 12.  RE: 2 fact table data prep help needed

    Bronze Contributor
    Posted Jul 20, 2018 04:11 AM
      |   view attached
    ​Thanks @Andrew Seward, it is really nice to have a working model as example. However, i still don't find a way how to adapt it to more realistic real life situation. In your example the only common thing between two fact tables is customer ​number/BPID and everything is clear.

    But let's say your transaction table contains data not just about the customer, but also about machine. So what I tried to do was to add a new column to extend order details by specifying randomly which machine (serial number) has been sold or serviced. Then I would like to analyse results by looking up let's say machine status and see how much of revenue came from warranty or non warrantly machines.


    So which of the 3 options would be the best to use and how?
    (modified file added)

    ------------------------------
    Domantas Gintauskas
    R&D Data Analyst
    GN Resound
    52829987
    ------------------------------

    Attachment(s)

    Academy - Online Interactive Learning from Experts


  • 13.  RE: 2 fact table data prep help needed

    Silver Contributor
    Posted Jul 20, 2018 12:30 PM
    @Domantas Gintauskas I kept it simple to explain the concept.  When you get to more complex data models you almost have to use the trick with the calculate specifying the other table you want to inherit the filter context from. The design of your Data Model is also extremely important as you grow the number of data sets.

    Some tips
    • I never connect two data table together directly. I a ALWAYS use a bridge table even for simple 1:1 relationships where one of the table is unique.  In the example below I even created a lookup table to filter the Date Table rather than filtering on the date table directly. I use this Fiscal Halfs table to filter my entire report (about 25 pages) but can override this filter in order to calculate trends for the last 24 months by just excluding this lookup table with all.
    • I use these bridge tables in slicers, axis filters wherever possible because it forces the relationship "downhill"
    • By downhill I mean the direction of the arrows. To help in model design I layout my Model screen to have the data tables on the bottom of the screen and lookup tables above.  this way all the arrows point down and its easier to follow the direction of the filter context as it WILL not float UPHILL unless you force it.

    See the picture below of my data model.
    In this example I don't need to do any tricks to force filter context since I am able to drive everything down form the and filters.


    Compare that to this model where my organization got messed up due to recovery from a backup after a shutdown.



    ------------------------------
    Andrew Seward
    Senior Product Marketing Engineer
    8454758636
    ------------------------------

    Academy - Online Interactive Learning from Experts