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),
)
mrothschild's avatar
mrothschild
Icon for Continued Contributor rankContinued Contributor
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

Sorry for not replying sooner, something appears to be going on with notifications and I have not received any for the last 4 days.

 

I think the second solution you arrived at with the help of Ashish is a cleaner one, it's similar to the second suggestion I posted, I just did not have the correct name for one of your tables.