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),
)
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
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
Icon for Continued Contributor rankContinued Contributor
7 years ago

Thank you.  This seems a bit brute force as well - but does work on my more complicated table. 

 

I'm not sure why someone suggested that unflattening my data would make programming easier - I don't think with a year's worth of dedicated effort I could have come up with your solution!  Thanks again.