Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi appreciate the assistance with reference to the Data Table below:
Is there a measure that captures a customer's minimum account balance:
1. at the lowest granularity (i.e. AccountNo level) but
2. at the customer level, it captures the minimum value of the sum of account balances for respective customers
Have included what the desired result should be.
At the moment, I am using powerpivot to extract the minimum balance at the deposit account level.
I then sum the minimum balance of a customers various deposit accounts to arrive at a minimum balance at the customer level using sumif. With data going to the millions of rows, this is just not a practical solution.
Thanks
Alfred
@atavo wrote:
Hi appreciate the assistance with reference to the Data Table below:
Is there a measure that captures a customer's minimum account balance:
1. at the lowest granularity (i.e. AccountNo level) but
2. at the customer level, it captures the minimum value of the sum of account balances for respective customers
Have included what the desired result should be.
At the moment, I am using powerpivot to extract the minimum balance at the deposit account level.
I then sum the minimum balance of a customers various deposit accounts to arrive at a minimum balance at the customer level using sumif. With data going to the millions of rows, this is just not a practical solution.
Thanks
Alfred
Is 300 for the customer Jack in Aug-17 a typo? My understanding is that the value shall be 250(50+200)? If my understanding is correct, you can create a measure as below. See more details in the attached pbix file.
Measure =
VAR summizedTbl =
SUMMARIZE (
'yourTable',
yourTable[Customer],
yourTable[AccountNo],
yourTable[YearMon],
"minBal", MIN ( yourTable[Balance] )
)
RETURN
SWITCH (
TRUE (),
ISFILTERED ( 'yourTable'[Customer] ) && ISFILTERED ( yourTable[AccountNo] ), MIN ( yourTable[Balance] ),
ISFILTERED ( 'yourTable'[Customer] ) && ISFILTERED ( yourTable[YearMon] ), SUMX ( summizedTbl, [minBal] ),
ISFILTERED ( 'yourTable'[Customer] )
&& NOT ( ISFILTERED ( yourTable[YearMon] ) ), CALCULATE (
MIN ( 'Table'[sumMinBal] ),
ALLEXCEPT ( yourTable, yourTable[Customer] )
),
MIN ( yourTable[Balance] )
)
@atavo sorry just to clarify do you mean minimum balance at the earliest date?
if that is what you mean using something like first date might work ie
Proud to be a Super User!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
85 | |
84 | |
65 | |
62 | |
55 |
User | Count |
---|---|
171 | |
109 | |
105 | |
73 | |
71 |