Forum Discussion

devitus's avatar
devitus
Frequent Visitor
5 years ago
Solved

Measure based on individual related values vs Calculated Column vs Power Query

Hi,   I have recently begun rebuilding my Power BI dataset which our organizations client-facing reporting relies on. One of my main targets for this rebuild is to avoid "merge tables" queries ...
  • Anonymous's avatar
    Anonymous
    5 years ago
    // The Spend Data 1, 2, 3... should be
    // one table as they have the same
    // layout. Just use PQ to union them.
    // You can add another column to this
    // union that will tell you which table
    // the data comes from.
    
    // Based on this assumption:
    
    [Gross Amount] =
    // This measure could be slow as it
    // needs to iterate the fact table. If you
    // want to have a blasingly fast one, you need
    // to precalculate the gross spend in
    // Power Query. It'll then be the fastest
    // since it will only have to sum up
    // a column with the gross spend. YOu can't
    // have a measure faster than that.
    SUMX(
    	FactTable,
    	var Fee1 = RELATED( CRM[Fee % 1] )
    	var Fee2 = RELATED( CRM[Fee % 2] )
    	var EndDate = RELATED( CRM[End Date] )
    	var Date_ = FactTable[Date]
    	var Fee2IsBlank = ISBLANK( Fee2 )
    	// TheFee codes the following logic:
    	// IF Fee % 2 is BLANK, then Fee % 1, else
    	// IF Date (from "UnionTable") is less or equal 
    	// to Fee % 1 end Date then Fee % 1, else Fee % 2.
    	var TheFee =
    		var ChooseFee1 = (Date_ <= EndDate)
    		var ChooseFee2 = 1 - ChooseFee1
    		return
    			Fee2IsBlank * Fee1
    			+ (1 - Fee2IsBlank)
    			* (
    				Fee1 * ChooseFee1
    				+ 
    				Fee2 * ChooseFee2
    			)
    	var TheNetSpend = FactTable[NetSpend]
    	var TheGrossSpend =
    		DIVIDE( TheNetSpend, 1 - TheFee )
    	return
    		TheGrossSpend
    )
  • Anonymous's avatar
    Anonymous
    5 years ago

     

    [Gross Amount] =
    SUMX(
    	SUMMARIZE(
    		FactTable,
    		FactTable[Date],
    		CRM[Fee % 1],
    		CRM[Fee % 2],
    		CRM[End Date]
    	),
    	var Fee1 = CRM[Fee % 1]
    	var Fee2 = CRM[Fee % 2]
    	var EndDate = CRM[End Date]
    	var Date_ = FactTable[Date]
    	var Fee2IsBlank = ISBLANK( Fee2 )
    	var TheFee =
    		var ChooseFee1 = (Date_ <= EndDate)
    		var ChooseFee2 = 1 - ChooseFee1
    		return
    			Fee2IsBlank * Fee1
    			+ (1 - Fee2IsBlank)
    			* (
    				Fee1 * ChooseFee1
    				+ 
    				Fee2 * ChooseFee2
    			)
    	var TheNetSpend = 
    		CALCULATE( SUM( FactTable[NetSpend] ) )
    	var TheGrossSpend = 
    		DIVIDE( TheNetSpend,  1 - TheFee )
    	return
    		TheGrossSpend
    )

     

    You could try the version above... might be faster but it depends on the data in your tables.