calculated columns are not populated in Bridge table correctly

[Anonymous]

Hi,

Although this question directly relates to Azure Analysis Service but still posting in the power bi.

I have a model in AAS and there I have to create some calculated columns. First I tried to create that in on of the actual table and it gave me circular dependency (because in my model maximum of my actual tables are connected through bridge tables). Then I tried to implement those calculated columns in the bridge table but the column is coming as blank in report. When I debug the DAX I found that the filter (which I am using in DAX ) is not working when I am calculating the column.

I am sharing my code and the model snap shot here.

CalculatedGTWDate_SLN::

=VAR GTWDate =

        SWITCH (

            TRUE (),

            ISBLANK ( MIN ( SFDC_Opportunity_NBV[GTWClosedDate] ) ), DATE ( 9999, 12, 31 ),

            DATE ( LEFT (

                CALCULATE (

                    MIN ( SFDC_Opportunity_NBV[GTWClosedDate] ),

                                                                        ALLEXCEPT (

                                                                                      SalesR48NBVBySolution,

                                                                                      CalculatedAccountNumber_NP[AccountNumber],

                                                                                      SalesR48NBVBySolution[Program],SalesR48NBVBySolution[L3Code]

                                                                        )

                ),

                4

            ), RIGHT (

                LEFT (

                    CALCULATE (

                        MIN ( SFDC_Opportunity_NBV[GTWClosedDate] ),

                                                                                      ALLEXCEPT (

                                                                                                    SalesR48NBVBySolution,

                                                                                                    CalculatedAccountNumber_NP[AccountNumber],

                                                                                                    SalesR48NBVBySolution[Program],SalesR48NBVBySolution[L3Code]

                                                                                      )

                    ),

                    7

                ),

                2

            ),1 )

        )

RETURN

   GTWDate

CalculatedFirstPurchaseDateAfterGTWCloseDate_SLN::

=VAR GTWDate = CalculatedAccountNumber_NP[CalculatedGTWDate_SLN]

RETURN

    CALCULATE (

        MIN ( SalesR48NBVBySolution[invoicedate] ),

        SalesR48NBVBySolution[InvoiceDate] >= GTWDate

    )

CalculatedCloseDateVSFirstPurchaseDate_SLN::

=DATEDIFF(CalculatedAccountNumber_NP[CalculatedFirstPurchasedateAfterGTWClosedDate_SLN], MAX(SFDC_Opportunity_NBV[ClosedMonth]),MONTH)

CalculatedAnnualization_SLN::

=VAR Denominator = (CalculatedAccountNumber_NP[CalculatedClosedDateVSFirstPurchaseDate_SLN]+ 1 )

VAR Sales =

    CALCULATE (

        SUM ( SalesR48NBVBySolution[TotalSales] ),

        FILTER (

            SalesR48NBVBySolution,

            SalesR48NBVBySolution[invoicedate] >= CalculatedAccountNumber_NP[CalculatedFirstPurchasedateAfterGTWClosedDate_SLN]

        )

    )

VAR R6AnchorSales =

    CALCULATE (

        SUM ( SalesR48NBVBySolution[TotalSales] ),

        FILTER (

            SalesR48NBVBySolution,

            SalesR48NBVBySolution[invoicedate]

                >= EDATE (

                     CalculatedAccountNumber_NP[CalculatedFirstPurchasedateAfterGTWClosedDate_SLN],

                    -6

                )

                && SalesR48NBVBySolution[invoicedate] < CalculatedAccountNumber_NP[CalculatedFirstPurchasedateAfterGTWClosedDate_SLN]

        )

    )

RETURN

    IF ( ISBLANK ( R6AnchorSales ),

        IF ( NOT (ISBLANK (  CalculatedAccountNumber_NP[CalculatedFirstPurchasedateAfterGTWClosedDate_SLN])

            )

                && ISBLANK ( R6AnchorSales ),

            DIVIDE ( Sales, Denominator ) * 12

        )

    )

Note::Other than CalculatedAnnualization_SLN the columns are populating fine in the report.

Could you please guide me how to resolve this

[technolog · Accepted solution]

Firstly, it's essential to understand that calculated columns are computed during the data loading phase in Power BI or Azure Analysis Services (AAS). This means that they are calculated row by row and don't have the full context of the entire table or other tables unless explicitly provided in the DAX formula.

From your description, it seems that the CalculatedAnnualization_SLN column isn't populating correctly. Let's break down the logic of this column:

You're calculating the Denominator which is based on the CalculatedClosedDateVSFirstPurchaseDate_SLN column.
You're calculating the Sales which is the sum of TotalSales for all rows where invoicedate is greater than or equal to CalculatedFirstPurchasedateAfterGTWClosedDate_SLN.
You're calculating the R6AnchorSales which is the sum of TotalSales for a specific date range.
Finally, you're returning a value based on the condition of R6AnchorSales.
The issue might be with the way the FILTER function is working in the context of a calculated column. When you're using the FILTER function within a calculated column, it's essential to remember that the filter is applied row by row.

Here's a suggestion to troubleshoot and potentially fix the issue:

Check Context: Ensure that the calculated columns you're referencing, like CalculatedFirstPurchasedateAfterGTWClosedDate_SLN, are in the same table as CalculatedAnnualization_SLN. If they're not, you might be losing context.

Simplify the DAX: Before diving deep, try simplifying your DAX for CalculatedAnnualization_SLN. Maybe start by just calculating Sales or R6AnchorSales to see if they return the expected values.

Use RELATED: If you're referencing columns from a different table, you might need to use the RELATED function to fetch the value for the current row context.

Check for BLANK Values: Your final RETURN in CalculatedAnnualization_SLN is based on the condition of R6AnchorSales being blank. Ensure that the logic leading up to this (especially the date comparisons in R6AnchorSales) is correct and not leading to unexpected blank values.

Debugging: Debugging DAX can be tricky. One way to debug is to create measures that break down each part of your logic. For example, create a measure just for Sales and another just for R6AnchorSales. This way, you can see the output of each part of your logic in a table or card visual in Power BI.

Lastly, remember that calculated columns consume memory, so always ensure that you need them. If possible, try to move some of this logic to measures or even back to the data source if it makes sense.

[Anonymous]

https://www.sqlbi.com/articles/avoiding-circular-dependency-errors-in-dax/