Forum Discussion

mrothschild's avatar
mrothschild
Continued 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...
  • Greg_Deckler's avatar
    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),
    )