Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
How we can create dynamic calculation on changing bucket as :
Row | BucketStatus | BucketAmiount |
1 | <1 | 1000 |
2 | 1--2 | >1000<2000 |
3 | >2 | >2000 |
and in future its change to
Row | BucketStatus | BucketAmiount |
1 | <2 | 2000 |
2 | 2--3 | >2000<3000 |
3 | >3 | >3000 |
I dont want to change the logic which I have created in BI.
XYZ = SWITCH(TRUE(),'Table'[Amount]<=BucketAmount_FirstRow,"BucketStatus_FirstRow",'Table'[Amount] is between BucketAmount_SecountRow,"BucketStatus_SecondRow" && 'Table'[Amount]<=BucketAmount_ThirdRow,"BucketStatus_ThirdRow")
Or in case I have created bucket, Its shd be dynamic.
Bucket1 = IF(Data[Amount]<=BucketAmount_FirstRow && YEAR(Data[Date])=(MAX('Date'[Year])),"BucketStatus_FirstRow",
IF(Data[Amount]>=BucketStatus_FirstRow && Data[Amount]< BucketStatus_SecondRow && YEAR(Data[Date])=MAX('Date'[Year]),"BucketStatus_SecondRow",
IF(Data[Amount]>BucketStatus_SecondRow && Data[Amount]<BucketStatus_ThirdRow && YEAR(Data[Date])=MAX('Date'[Year]),"BucketStatus_ThirdRow", BLANK()))))
Thanks,
R_
Solved! Go to Solution.
Hi @R_,
Based on your rule table, I write a column to auto generate the bucket state.(For my scenario, you only need to write two row to limit the amount range)
Calculate column:
Bucket State = VAR Min_Amount = LOOKUPVALUE ( 'Dim Bucket'[BucketAmiount], 'Dim Bucket'[Row], MINX ( ALL ( 'Dim Bucket'[Row] ), [Row] ) ) VAR temp = LOOKUPVALUE ( 'Dim Bucket'[BucketAmiount], 'Dim Bucket'[Row], MAXX ( ALL ( 'Dim Bucket'[Row] ), [Row] ) ) VAR Max_Amount = RIGHT ( temp, LEN ( temp ) - 1 ) RETURN IF ( [Amount] <= VALUE ( Min_Amount ), LOOKUPVALUE ( 'Dim Bucket'[BucketStatus], 'Dim Bucket'[Row], MINX ( ALL ( 'Dim Bucket'[Row] ), [Row] ) ), IF ( [Amount] >= VALUE ( Max_Amount ), LOOKUPVALUE ( 'Dim Bucket'[BucketStatus], 'Dim Bucket'[Row], MAXX ( ALL ( 'Dim Bucket'[Row] ), [Row] ) ), INT ( [Amount] / 1000 ) & "--" & ( INT ( [Amount] / 1000 ) + 1 ) ) )
Notice: the min and max status are look from bucket range table, other median parts are auto generated.
Regards,
Xiaoxin Sheng
Hi @R_,
Based on your rule table, I write a column to auto generate the bucket state.(For my scenario, you only need to write two row to limit the amount range)
Calculate column:
Bucket State = VAR Min_Amount = LOOKUPVALUE ( 'Dim Bucket'[BucketAmiount], 'Dim Bucket'[Row], MINX ( ALL ( 'Dim Bucket'[Row] ), [Row] ) ) VAR temp = LOOKUPVALUE ( 'Dim Bucket'[BucketAmiount], 'Dim Bucket'[Row], MAXX ( ALL ( 'Dim Bucket'[Row] ), [Row] ) ) VAR Max_Amount = RIGHT ( temp, LEN ( temp ) - 1 ) RETURN IF ( [Amount] <= VALUE ( Min_Amount ), LOOKUPVALUE ( 'Dim Bucket'[BucketStatus], 'Dim Bucket'[Row], MINX ( ALL ( 'Dim Bucket'[Row] ), [Row] ) ), IF ( [Amount] >= VALUE ( Max_Amount ), LOOKUPVALUE ( 'Dim Bucket'[BucketStatus], 'Dim Bucket'[Row], MAXX ( ALL ( 'Dim Bucket'[Row] ), [Row] ) ), INT ( [Amount] / 1000 ) & "--" & ( INT ( [Amount] / 1000 ) + 1 ) ) )
Notice: the min and max status are look from bucket range table, other median parts are auto generated.
Regards,
Xiaoxin Sheng
User | Count |
---|---|
127 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |