Forum Discussion

data_mp_97's avatar
data_mp_97
Frequent Visitor
10 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 is the following:

 

field(5752; "Completely Shipped"; Boolean)
{
      CalcFormula = min("Sales Line"."Completely Shipped" where("Document Type" = field("Document Type"),
                                                                "Document No." = field("No."),
&nbsp;                                                               Type = filter(<> " "),
&nbsp;                                                               "Location Code" = field("Location Filter")));
&nbsp;     Caption = 'Completely Shipped';
&nbsp;     Editable = false;
&nbsp;     FieldClass = FlowField;

I have replicated it like this in DAX:

Completely Shipped =
VAR CurrentDocumentType = SELECTEDVALUE('salesheader36'[DocumentType])
VAR CurrentDocumentNo = SELECTEDVALUE('salesheader36'[No])

VAR LineasFiltradas =
    FILTER(
        'salesline37',
        'salesline37'[DocumentType] = CurrentDocumentType &&
        'salesline37'[DocumentNo] = CurrentDocumentNo &&
        'salesline37'[Type] <> BLANK()
    )

VAR MinCompletado =
    MINX(LineasFiltradas, INT('salesline37'[CompletelyShipped]))

RETURN
    MinCompletado

I skipped the locationfilter part since it is a flowfilter that I was told is not necessary.The results of my DAX formula, when I apply it to the general formula, do not match the results in Business Central.

Any ideas? I have tried many ways and it does not give me the result..



 

  • v-pnaroju-msft's avatar
    v-pnaroju-msft
    10 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.

     

9 Replies

  • johnt75's avatar
    johnt75
    Super User
    10 months ago

    If you are creating a calculated column then SELECTEDVALUE will not work - that works only in a filter context, not in a row context. If you are creating the column on the table 'salesheader36' then you can use

    Completely Shipped =
VAR CurrentDocumentType = 'salesheader36'[DocumentType]
VAR CurrentDocumentNo = 'salesheader36'[No]
VAR LineasFiltradas =
    FILTER (
        'salesline37',
        'salesline37'[DocumentType] = CurrentDocumentType
            && 'salesline37'[DocumentNo] = CurrentDocumentNo
            && 'salesline37'[Type] <> BLANK ()
    )
VAR MinCompletado =
    MINX ( LineasFiltradas, INT ( 'salesline37'[CompletelyShipped] ) )
RETURN
    MinCompletado
    • data_mp_97's avatar
      data_mp_97
      Frequent Visitor
      10 months ago

      No, but I was creating a DAX measure (Completely Shipped), not a calculated column.Then I use that DAX measure here:

      VentasDistriNoAlbaranadas_Final22(CB)=
      CALCULATE(DISTINCTCOUNT('salesheader36'[No]),
      'salesheader36'[PostingDate] >= DATE(2025, 8, 1),
      'salesheader36'[PostingDate] <= DATE(2025, 10, 2),
      'salesheader36'[$Company] = "DIST",
      FILTER('salesline37',[Completely Shipped] = 0))

      Maybe the error can be seen here..

  • grazitti_sapna's avatar
    grazitti_sapna
    Super User
    10 months ago

    Hi data_mp_97 ,

    You can create a calculated column first,
    Completely Shipped =
    VAR CurrentDocumentType = 'salesheader36'[DocumentType]
    VAR CurrentDocumentNo = 'salesheader36'[No]
    VAR LineasFiltradas =
    FILTER (
    'salesline37',
    'salesline37'[DocumentType] = CurrentDocumentType
    && 'salesline37'[DocumentNo] = CurrentDocumentNo
    && NOT ISBLANK ( 'salesline37'[Type] )
    )
    RETURN
    MINX ( LineasFiltradas, INT ( 'salesline37'[CompletelyShipped] ) )


    and thn create a meausure taking the calculated column base:- 

    VentasDistriNoAlbaranadas_Final22(CB) =

    CALCULATE (
    DISTINCTCOUNT ( 'salesheader36'[No] ),
    'salesheader36'[PostingDate] >= DATE ( 2025, 8, 1 ),
    'salesheader36'[PostingDate] <= DATE ( 2025, 10, 2 ),
    'salesheader36'[$Company] = "DIST",
    'salesheader36'[Completely Shipped] = 0
    )


    🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.

    💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.

    🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.

    🔗 Curious to explore more? [Discover here].

    Let’s keep building smarter solutions together!

  • v-pnaroju-msft's avatar
    v-pnaroju-msft
    Community Support
    10 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
    Community Support
    10 months ago

    Hi data_mp_97,

    We would like to follow up and see whether the details we shared have resolved your problem.
    If you need any more assistance, please feel free to connect with the Microsoft Fabric community.

    Thank you.

  • v-pnaroju-msft's avatar
    v-pnaroju-msft
    Community Support
    10 months ago

    Hi data_mp_97,

    We are following up to see if what we shared solved your issue. If you need more support, please reach out to the Microsoft Fabric community.

    Thank you.

  • v-pnaroju-msft's avatar
    v-pnaroju-msft
    Community Support
    10 months ago

    Hi data_mp_97,

    We wanted to see if the information we gave helped fix your problem. If you need more help, please feel free to contact the Microsoft Fabric community.

    Thank you.