Forum Discussion

data_mp_97's avatar
data_mp_97
Frequent Visitor
11 months ago
Solved

Problem calculating flowfield in DAX

Hello everyone,I have a problem calculating a flowfield (calculated column that cannot be exported) that comes from Business Central. The formula for this flowfield in Visual Studio and with AL code...
  • v-pnaroju-msft's avatar
    v-pnaroju-msft
    11 months ago

    Thankyou, johnt75, and grazitti_sapna, for your responses.

    Hi data_mp_97,

    We appreciate your inquiry through the Microsoft Fabric Community Forum.

    Based on my understanding, the discrepancy arises because the Business Central FlowField is evaluated at the header level, that is, it computes the minimum of the related sales lines. In your DAX, the measure was being evaluated at the line level, which caused a context mismatch. In addition, the FlowField excludes empty Type values and may include a Location filter, which were not fully replicated in your measure.
    Please follow the suggested approach below which may help to resolve the issue:
    1. Compute the FlowField logic at the header level and then apply it within your calculation, as shown:
    VentasDistriNoAlbaranadas_Final22(CB) =
    CALCULATE(
    DISTINCTCOUNT('salesheader36'[No]),
    'salesheader36'[PostingDate] >= DATE(2025, 8, 1),
    'salesheader36'[PostingDate] <= DATE(2025, 10, 2),
    'salesheader36'[$Company] = "DIST",
    FILTER (
    VALUES('salesheader36'[No]),
    VAR MinComp =
    CALCULATE(
    MINX(
    FILTER (
    'salesline37',
    TRIM(COALESCE('salesline37'[Type], "")) <> ""
    && 'salesline37'[LocationCode] = SELECTEDVALUE(LocationTable[LocationCode])
    ),
    IF('salesline37'[CompletelyShipped],1,0)
    )
    )
    RETURN COALESCE(MinComp, 0) = 0
    )
    )
    Using VALUES('salesheader36'[No]) ensures that the calculation is performed per header, analogous to FlowFields in Business Central. The inner MINX replicates the FlowField’s minimum across related sales lines. The TRIM(COALESCE(...)) <> "" expression corresponds to Business Central’s Type <> " " condition.

    For further details, please refer to the link below:
    FlowFields overview - Business Central | Microsoft Learn

    We hope this information helps to resolve the issue. If you have any further queries, please feel free to contact the Microsoft Fabric Community.
    Thank you.

     

v-pnaroju-msft's avatar
v-pnaroju-msft
Icon for Community Support rankCommunity Support
11 months ago

Thankyou, johnt75, and grazitti_sapna, for your responses.

Hi data_mp_97,

We appreciate your inquiry through the Microsoft Fabric Community Forum.

Based on my understanding, the discrepancy arises because the Business Central FlowField is evaluated at the header level, that is, it computes the minimum of the related sales lines. In your DAX, the measure was being evaluated at the line level, which caused a context mismatch. In addition, the FlowField excludes empty Type values and may include a Location filter, which were not fully replicated in your measure.
Please follow the suggested approach below which may help to resolve the issue:
1. Compute the FlowField logic at the header level and then apply it within your calculation, as shown:
VentasDistriNoAlbaranadas_Final22(CB) =
CALCULATE(
DISTINCTCOUNT('salesheader36'[No]),
'salesheader36'[PostingDate] >= DATE(2025, 8, 1),
'salesheader36'[PostingDate] <= DATE(2025, 10, 2),
'salesheader36'[$Company] = "DIST",
FILTER (
VALUES('salesheader36'[No]),
VAR MinComp =
CALCULATE(
MINX(
FILTER (
'salesline37',
TRIM(COALESCE('salesline37'[Type], "")) <> ""
&& 'salesline37'[LocationCode] = SELECTEDVALUE(LocationTable[LocationCode])
),
IF('salesline37'[CompletelyShipped],1,0)
)
)
RETURN COALESCE(MinComp, 0) = 0
)
)
Using VALUES('salesheader36'[No]) ensures that the calculation is performed per header, analogous to FlowFields in Business Central. The inner MINX replicates the FlowField’s minimum across related sales lines. The TRIM(COALESCE(...)) <> "" expression corresponds to Business Central’s Type <> " " condition.

For further details, please refer to the link below:
FlowFields overview - Business Central | Microsoft Learn

We hope this information helps to resolve the issue. If you have any further queries, please feel free to contact the Microsoft Fabric Community.
Thank you.