Forum Discussion

mrothschild's avatar
mrothschild
Icon for Continued Contributor rankContinued Contributor
7 years ago
Solved

help with unflattened data

I'm new to BI and I've really twisted my head in knots here.  To try to implement best practices, I'm moving from a flattened data table (from Excel) to unflatten my data to make it more efficient an...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    7 years ago

    This might be a bit convoluted, but seems to work:

     

    Measure 4 = 
VAR __table = FILTER('Table7',[Count]>0)
VAR __table1 = GROUPBY(__table,[Asset ID],"__max",MAXX(CURRENTGROUP(),[Count]),"__base",SUMX(CURRENTGROUP(),[Base]))
VAR __table2 = ADDCOLUMNS(__table1,"__baseMax",MAXX(FILTER(ALL('Table7'),'Table7'[Asset ID]=EARLIER([Asset ID]) && 'Table7'[Count]=[__max]),[Base]))
VAR __table3 = ADDCOLUMNS(__table2,"__baseFinal",[__base] - [__baseMax])
RETURN
SUMX(__table3,[__baseFinal])

    See Table7 of attached.

  • mrothschild's avatar
    mrothschild
    7 years ago

    Ashish - thanks to your help on a different question I had, I was able to figure out the intended result as follows.  The RELATED function did the trick.  

     

    Thanks

     

    CALCULATE(
	SUM(AssetReturnTable[Base]),
    ALLSELECTED(AssetReturnTable[Base]),
    FILTER(AssetReturnTable,AssetReturnTable[Count]>0),
    FILTER(AssetReturnTable,AssetReturnTable[Count]<RELATED(SummaryInputTable[Lease Term (months)])+1),
)
d_gosbell's avatar
d_gosbell
Icon for Super User rankSuper User
7 years ago

I'm not sure if I'm missing anything, but I would have though you could do this with a calculation like the following

Measure = SUMX(
  AssetReturn,
  var _maxCnt = CALCULATE(MAX(AssetReturn[Count]), ALLEXCEPT(AssetReturn,AssetReturn[Asset ID]))
  return if( AssetReturn[Count] = _maxCnt, AssetReturn[Base])
)
 
Although you could probably match your original logic by using the related function to "lookup" the lease term in the SummaryInput table

Measure = SUMX(
  AssetReturn,
  var _leaseTerm = RELATED(SummaryInput[Lease Term (months)] )
  return if( AssetReturn[Count] > 0 && AssetReturn[Count] < _leaseTerm, AssetReturn[Base])
)
mrothschild's avatar
mrothschild
Icon for Continued Contributor rankContinued Contributor
7 years ago

Thanks, but the first option you offered doesn't result in the desired output and the second throws an error that says: "The column SUmmaryInput[Lease Term. ..] either doesnt' exist or doesn't have a relaitonship to any table available in the current context."

 

I'm interested in the direction you're going because it seems like a much more transferrable approach than the solution that is currently working.