Forum Discussion
dax expression help needed
Hi All,
Help me with a measure-
Current dax
Forecast to complete =
VAR FC_Terminal =
SUMX(
FILTER(
CostItem,
CostItem[Is_terminal] = TRUE()
),
[Remaining Qty] * [CE Rate]
)
RETURN
IF(
SELECTEDVALUE(CostItem[Is_terminal]) = FALSE(),
FC_Terminal,
[Remaining Qty] * [CE Rate]
)
How to modify the dax to include the logic -
when Forecast_method is None don't do [Remaining Qty] *[CE Rate] OR show 0
else
do the multiply
Forecast_to_complete_test =
VAR FC_Terminal =
SUMX(
FILTER(
CostItem,
CostItem[Is_terminal] = TRUE()
),
[Remaining Qty] * [CE Rate]
)
VAR ForecastMethod =
FIRSTNONBLANK(CurrentProjectForecastDetail[Forecast_Method], 1)
RETURN
IF(
SELECTEDVALUE(CostItem[Is_terminal]) = FALSE(),
FC_Terminal,
IF(
ForecastMethod <> "None",
FC_Terminal,
0
)
)
I tried this , but not working
Hi ashmitp869 ,
Please try below DAX measure:
Forecast_to_complete_test_1 =
VAR ForecastMethod_CurrentRow =
SELECTEDVALUE(CurrentProjectForecastDetail[Forecast_Method], "None")
VAR RowValue =
IF(
ForecastMethod_CurrentRow = "None",
0,
[Remaining Qty] * [CE Rate]
)
RETURNIF(
ISINSCOPE(CostItem[Cost_item_id]),
RowValue,
SUMX(
FILTER(
CostItem,
CostItem[Is_terminal] = TRUE()
&& RELATED(CurrentProjectForecastDetail[Forecast_Method]) <> "None"
),
[Remaining Qty] * [CE Rate]
)
)
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Regards,
B Manikanteswara Reddy
8 Replies
May be I am getting that in that line, but how to work it out in terminal total
The Terminal is 1.5.1 Project Managent Team with 1,951,120 it should be 1,316,029Hi,
I am not sure how your semantic model looks like, but please try something like below whether it works.
expected result measure: = SUMX ( SUMMARIZE ( fact_table, dim_table[column_in_matrix_01], dim_table[column_in_matrix_02] ), [your_fixed_measure] )Hi ashmitp869 ,
Thank you for reaching out to Microsoft Fabric Community Forum
Ashish_Mathur Jihwan_Kim Thank you for your quick responses.
Please try the below DAX measure.
Forecast to complete (Final) =VAR FC_Terminal =SUMX(FILTER(ADDCOLUMNS(CostItem,"ForecastMethod", SELECTEDVALUE(CurrentProjectForecastDetail[Forecast_Method], "None")),[Is_terminal] = TRUE() &&[ForecastMethod] <> "None"),[Remaining Qty] * [CE Rate])VAR ForecastMethod_CurrentRow =SELECTEDVALUE(CurrentProjectForecastDetail[Forecast_Method], "None")RETURNIF(SELECTEDVALUE(CostItem[Is_terminal]) = FALSE(),FC_Terminal,IF(ForecastMethod_CurrentRow = "None",0,[Remaining Qty] * [CE Rate]))If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Regards,
B Manikanteswara Reddy
Hi Anonymous
I tried your expression.
It is showing 0 when Forecast method is None but the total is not summing up
it should give a result of amount 1,316,029
Hi,
Share the download link of the PBI file. Explain the problem in simple English. Show the expected result clearly.
Hi Ashish_Mathur
Sample file provided.
https://github.com/suvechha/samplepbi/blob/main/sample%20(2)%20-%20Copy.pbix
Expected result
When Forecast method is None show 0.
And
total Sum should be 1,316,029 instead of 1,951,120Thanks
Hi,
Used these measures
Measure 2 = [Remaining Qty] * [CE Rate]Measure 4 = MAX(CurrentProjectForecastDetail[Forecast_method])Measure 3 = SUMX(FILTER(VALUES(CostItem[CBS Level 4]),[Measure 4]<>"None"),[Measure 2])Hope this helps.
