Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
Asset ID | Count | Base |
Alpha | 0 | -100 |
Alpha | 1 | 10 |
Alpha | 2 | 10 |
Alpha | 3 | 10 |
Alpha | 4 | 150 |
Bravo | 0 | -150 |
Bravo | 1 | 12.5 |
Bravo | 2 | 12.5 |
Bravo | 3 | 12.5 |
Bravo | 4 | 12.5 |
Bravo | 5 | 12.5 |
Bravo | 6 | 200 |
Charlie | 0 | -50 |
Charlie | 1 | 4 |
Charlie | 2 | 4 |
Charlie | 3 | 65 |
Solved! Go to Solution.
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.
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), )
Hi,
I am confused about what result do you want - 100.5 or 415?
Either one will do. I can subtract the 415 from the column total to get to the same result.
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.
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), )
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.
Thanks - but this produces 200, I need the sum of the maximum count at eachl AssetID
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]) )
Measure = SUMX( AssetReturn, var _leaseTerm = RELATED(SummaryInput[Lease Term (months)] ) return if( AssetReturn[Count] > 0 && AssetReturn[Count] < _leaseTerm, AssetReturn[Base]) )
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.
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.
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.