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.
devitus
5 years agoFrequent Visitor
Thank you Anonymous it has worked with the measure you have suggested! I can see that it slows down table rendering times, so I'll test its speed for a while and will revert to PQ option for Gross Spend if the measure proves unsustainable.
Thanks again!
Anonymous
5 years agoNot applicable
[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.