Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi expert,
I am stucking on this issue for 2 days, could you please help?
I have a measure like this:
Actual1 =
Var Var_REPORT_TYPE = "02"
Var Var_RATE_TYPE_CD = "02"
Var Var_Actual_By_Region =
CALCULATE(SUM(Table1[AMT])
,FILTER('Table1',[REPORT_TY] = Var_REPORT_TYPE)
,FILTER('Table1',[RATE_TYPE_CD] = IF(HASONEVALUE(FORBI_M_RATE_TYPE[RATE_TYPE_CD]),MAX(RateMaster[RATE_TYPE_CD]),Var_RATE_TYPE_CD))
,FILTER(Table1,[REGION_NM] = VALUES(RegionMaster[REGION_NM]))
,FILTER(IndicatorTBL,IndicatorTBL[TOTAL_DISPLAY_FLG]=1)
)
Return
--Actual by Actual (in case blank, return 0)
IF(VALUES(RegionMaster[REGION_NM]) <> "TOTAL"
,IF(
LOOKUPVALUE(
IndicatorTBL[TOTAL_DISPLAY_FLG]
,IndicatorTBL[BI_INDICATOR_CD]
,MAX(Table1[BI_INDICATOR_CD]))=1
,Var_Actual_By_Region+0
,Var_Actual_By_Region)
--TOTAL's Actual
,CALCULATE(
CALCULATE(SUM(Table1[AMT])
,FILTER('Table1',[REPORT_TY] = Var_REPORT_TYPE)
,FILTER('Table1',[RATE_TYPE_CD] = IF(HASONEVALUE(RateMaster[RATE_TYPE_CD]),MAX(RateMaster[RATE_TYPE_CD]),Var_RATE_TYPE_CD)))
,ALL(RegionMaster)
)+0
)
And then, I need to get specific data out of the above measure, so I use the below measure
ActualPL_1a =
Var Var_BI_INDICATOR_CD = "1a"
Var Var_REPORT_TYPE = "02"
Var Var_RATE_TYPE_CD = "02"
var test = [Actual1]
Return
IF(VALUES(V_FORBI_M_BI_REGION_PL[REGION_NM]) <> "TOTAL"
,CALCULATE(
CALCULATE(
[Actual1]
,FILTER(Table1,[BI_INDICATOR_CD] = Var_BI_INDICATOR_CD)
)
,ALL(IndicatorTBL)
)
,
CALCULATE(
CALCULATE(
test
,FILTER(Table1,[BI_INDICATOR_CD] = Var_BI_INDICATOR_CD)
)
,ALL(IndicatorTBL)
,ALL(RegionMaster)
)
)
In ActualPL_1a, for the TRUE part of the if (<> "TOTAL" is true) works well (can filter indicator code = '1a'), but for the FALSE part of the IF (In case of TOTAL), the data cannot be filtered by indicator code (=1a) as well as ALL(IndicatorTBL)
Can anyone help me :(((
Thank so much
sorry, I don't think HASONEVALUE works there.
@Anonymous
Please provide more details about your visual, perhaps a screenshot to find out what possibly works.
Hi @Anonymous
you can use
IF ( HASONEVALUE ( the column that you are slicing by ), regular calculation, total calculation )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |