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 and theoretically ease future programming.  

 

This measure from my flattened data produces desired results:

 

CALCULATE(
    SUM('FlatTable'[Base]),
    ALLSELECTED('FlatTable'[Asset ID]),
Filter('FlatTable','FlatTable'[Count]>0),
FIlter('FlatTable','FlatTable'[Count]<'FlatTable'[Lease Term (months)])
)
 
 
In my unflattened data, I have Asset ID linking two tables: SummaryInput and AssetReturn.  From above, [Base] and [Count] are on the AssetReturn table, but [Count] resets at 0 for each [Asset ID] and counts up to the highest number = [Lease Term (months)] 
 
[Lease Term (months)] is on the SummaryInput table, but not on the AssetReturn table.  
 
So I'm trying to figure out the right GROUPBY, SUMMARIZE, FILTER, etc. to arrive at the result I'm trying to achieve, which is the sum of all [Base] except for 0 and the last associated period for a given [Asset ID], i.e., if the data was like below, the desired output result measure would produce 100.5 = sum (3*10 + 5*12.5 + 2*4) 
 
Another way to think about this is to return a value of (150 + 200 + 65), which is a conditional MAX function on [Count], but it's the MAX only associated with a given [Asset ID] as opposed to the entire column.  Or, I assume, but haven't figured out how, I could re-flatten the data within Power BI, to add a column with [Lease Term (months)] to below and then compare [Count] and [Lease Term (months] as I did originally, but I assume that just moves me away from best practices again.
 
 
 
Asset IDCountBase
Alpha0-100
Alpha110
Alpha210
Alpha310
Alpha4150
Bravo0-150
Bravo112.5
Bravo212.5
Bravo312.5
Bravo412.5
Bravo512.5
Bravo6200
Charlie0-50
Charlie14
Charlie24
Charlie365
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),
)

10 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community 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
      Continued 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.  

  • d_gosbell's avatar
    d_gosbell
    Super 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
      Continued 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.

       

    • mrothschild's avatar
      mrothschild
      Continued Contributor
      7 years ago

      Either one will do.  I can subtract the 415 from the column total to get to the same result.

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        7 years ago

        Hi,

         

        This is the measure i wrote

         

        =Measure = SUMX(FILTER(SUMMARIZE(VALUES(Data[Count]),Data[Count],"ABCD",CALCULATE(MAX(Data[Count]),ALL(Data[Count])),"EFGH",SUM(Data[Base])),[ABCD]=Data[Count]),[EFGH])

         

        Hope this helps.