Forum Discussion

vgeldbr's avatar
vgeldbr
Icon for Helper IV rankHelper IV
3 years ago

Data Model and DAX Measure for different grain fact tables

I am struggling with what I think is a combination of data model and DAX measure challenge. See data model picture below. Essentially I have tables with:

1. All projects 

2. Financials (plan, forecast and actuals for all projects at month grain)

3. Actuals at week grain but at the individual transaction level

 

I need to make a calculation where I need to subtract an amount from the Actuals as represented in the Financials table which is represented in the Actuals table as a certain type of invoice. I think I am fine writing the measure but my challenge is the correct way to model the data.

 

The Financials and the Actuals tables have a many-to-many relationship. I think I can use an inactive relationship in the measure or set up the data model better than I have for this sort of scenario.

 

Any suggestions (or questions) to get me started down the right route please?

 

Need Help

6 Replies

  • JoeBarry's avatar
    JoeBarry
    Icon for Solution Sage rankSolution Sage
    3 years ago

    Hi vgeldbr 

     

    Within the Daily Projects, is the Project ID Unique? remove any relat

    If it is, then create a one to many relationship between the Daily Projects and the ProjectID in the Project Financials and the Portfolio Actuals.

     

    The same with DIM Date, Calenderdate with the Date columns in both Project Financials and the Portfolio Actuals. Delete any unnecessary relationships.

     

    The Idea is using the Star Schema https://learn.microsoft.com/en-us/power-bi/guidance/star-schema

     

    If the tables share a Dimension Table, then you can create comparisons with those dimensions on both tables

     

    In visuals and slicers, you would use the columns from the dimension tables, then add the measures to the visuals from the Fact tables, then you can compare

     

    thanks

    Joe

     

    If this post helps, then please Accept it as the solution

    • vgeldbr's avatar
      vgeldbr
      Icon for Helper IV rankHelper IV
      3 years ago

      JoeBarry thanks.

       

      1. Yes, ProjectID is unique in Daily Projects

      2. There is already a 1:* relationship between Daily Projects and Daily ProjectFinancial and between Daily Projects and Actuals (the latter is shown incorrecty in screenshot as I was testing something else)

      3. There is 1:* between each of the two date tables and both ProjectFinancial and Actuals

       

      New screenshot should reflect what you suggest. 

       

      I'm still not clear, however, how to write a the measures I need. Currently I have:

       

      1. Total Actuals based on ProjectFinancial 

      CALCULATE( SUM( 'Daily_ProjectFinancial'[MTD] ), 'Daily_ProjectFinancial'[Dimension] = "Actuals", KEEPFILTERS( NOT CONTAINSSTRING( 'Daily_ProjectFinancial'[Budgetcomponent], "Total" ) ) )

       

      2. Total Actuals from Actuals

      CALCULATE( SUM( 'Portfolio Actuals'[ExpenseUSDCurrentFYTD] ) )

       

      3. I need to deduct certain Actuals from the summarised actuals:

      VAR CurrentFY = CALCULATE( MAX( 'DimDate'[FiscalYear] ), ALL( 'DimDate' ), 'Date Periods'[CalendarDate] = TODAY( ) ) VAR PreviousFY_PE = CALCULATE( [Actuals Total], 'Portfolio Actuals'[Product Enablement Flag] = TRUE, KEEPFILTERS('Portfolio Actuals'[Budgetcomponent]) ) VAR Result = SWITCH( TRUE( ), MAX( 'DimDate'[FiscalYear] ) < CurrentFY, [ProjectTotalActuals] - PreviousFY_PE, MAX( 'DimDate'[FiscalYear] ) >= CurrentFY, [ProjectTotalPlan] ) RETURN Result 

       

      Many thanks!

      • JoeBarry's avatar
        JoeBarry
        Icon for Solution Sage rankSolution Sage
        3 years ago

        I will to decipher your measures. If I'm not mistaken, you want to show a different total based on a certain date. 

         

         

        1. Total Actuals based on ProjectFinancial (I'm presuming there is a Total Value you don't want to consider? The  'Daily_ProjectFinancial'[MTD] column, is this the only value column? Do you have a column and a row for each transaction? 

         

        Total Actuals = CALCULATE(SUM( 'Daily_ProjectFinancial'[MTD]), KEEPFILTERS( 'Daily_ProjectFinancial'[Dimension] = "Actuals" &&  'Daily_ProjectFinancial'[Budgetcomponent] <> "Total"

         

        2. CALCULATE( SUM( 'Portfolio Actuals'[ExpenseUSDCurrentFYTD] ) ) Remove the Calculate here

         

        3. If I'm not mistaken, you want to show a different total based on a certain date.  

         

         

        VAR CurrentFY = What should be the parmeters here? Do you want to see the Max Fiscal year based on Todays Year?

         

        VAR PreviousFY_PE = Is this the Portfolio Actuals where the [Product Enablement Flag] = TRUE?

         

        I don't understand this part of the VAR KEEPFILTERS('Portfolio Actuals'[Budgetcomponent]) 

         

        If you could clear these up for me, maybe I can help further