Forum Discussion
devitus
5 years agoFrequent Visitor
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 ...
- Anonymous5 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 ) - Anonymous5 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.
Anonymous
5 years agoNot applicable
// 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] = // measure that should be universal and fast
SUMX(
VALUES( CRM[Fee %] ),
var TheFee = CRM[Fee %]
var TheNetSpend =
CALCULATE( SUM( UnionTable[NetSpend] ) )
var TheGrossSpend =
// Fees should be decimals in [0, 1).
DIVIDE( TheNetSpend, 1 - TheFee )
return
TheGrossSpend
)
Using this setup you'll be also able to slice by the table. Something that is not achievable (at least easily) in your current model.
devitus
5 years agoFrequent Visitor
Thank you for your suggestion. I will test it and get back with findings!
Really appreciate your help so far.