Forum Discussion

ashmitp869's avatar
ashmitp869
Icon for Responsive Resident rankResponsive Resident
1 year ago
Solved

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() ), ...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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]

        )
    RETURN

    IF(

        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

ashmitp869's avatar
ashmitp869
Icon for Responsive Resident rankResponsive Resident
1 year ago

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,029

 

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    1 year ago

    Hi,

    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]
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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")
    RETURN
    IF(
        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

    • ashmitp869's avatar
      ashmitp869
      Icon for Responsive Resident rankResponsive Resident
      1 year ago

      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

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        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]

            )
        RETURN

        IF(

            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