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, forec...
Need Help
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

     

     

     

     

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

      You can ignore the date element of the final measure. All it is doing is taking total actuals for previous fiscal year but taking total actuals - PE for the current fiscal year. The relevant portion is:

      VAR PreviousFY_PE = CALCULATE( [Actuals Total], 
'Portfolio Actuals'[Product Enablement Flag] = TRUE, 
KEEPFILTERS('Portfolio Actuals'[Budgetcomponent]) 
) 
VAR Result = SWITCH( TRUE( ), 
[ProjectTotalActuals] - PreviousFY_PE 
) 
RETURN 
Result

      1. Total Actuals based on Project_Financial has a $ value but there is a another column [Dimension] which indicates if the line relates to Actuals, Forecast or Plan and also a column [Budgetcomponent] that indicates if it is one of multiple cost types, a sub-total for those cost type or the total for the month across all cost types. I don't control this data unfortunately. The measure selects only [Dimension] = "Actuals" and the detail lines ignoring all sub-totals and totals. The result is a row for each project for each month for each cost type. The measure works fine across many reports today.

       

      2. Can you help me understand why I should remove the calculate? This measure is used in other constexts.

       

       

       

       

       

       

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

        I will start with this

         

        Total Actuals from Actuals

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

        In this case, there is no need to use Calculate as you are not using for example a FILTER/KEEPFILTERS function.

        SUM(Portfolio Actuals'[ExpenseUSDCurrentFYTD] )

         is enough here

         

        For the first VAR you can filter on two elements from the same table 

         

        CALCULATE( [Actuals Total], 
KEEPFILTERS('Portfolio Actuals'[Product Enablement Flag] = TRUE && 
'Portfolio Actuals'[Budgetcomponent] = "Actuals")

         

        Hope this helps