Forum Discussion

emarome94's avatar
emarome94
Helper I
3 years ago
Solved

Circular Dependencies - Calculated Table

Hi everyone, 

I'm creating a table with the following SUMMARIZE functions:

 

 

The above Dax expression actually create a Table for the purpose I have in mind. 

The problem is that I receive a circular dependecie when I try to create a relationship between the new table 'Table' and the 'Query1' table (which of Customer Number unique values come from)  by using the customer number columns

 


Can someone help me to fix the DAX expression? 

Thanks in advance, 

E

General Comment
Show and Tell
Tips and Tricks
  • johnt75's avatar
    johnt75
    3 years ago

    Don't filter the blank values out and then run the below in DAX Studio or Tabular Editor

    EVALUATE
ADDCOLUMNS (
    DISTINCT ( 'Query1'[Customer number] ),
    "Customer type", IF ( CALCULATE ( [Total quantity no fob] ) >= 10000, "Top", "No top" ),
    "Total sales", CALCULATE ( [Total sales no fob] ),
    "Total quantity", CALCULATE ( [Total quantity no fob] )
)

    By sorting the results on customer number in first ascending and then descending order you should be able to see whether there are any blanks in the result.

    You could also try

    DEFINE
    VAR T1 =
        ADDCOLUMNS (
            ALLNOBLANKROW ( 'Query1'[Customer number] ),
            "Customer type", IF ( CALCULATE ( [Total quantity no fob] ) >= 10000, "Top", "No top" ),
            "Total sales", CALCULATE ( [Total sales no fob] ),
            "Total quantity", CALCULATE ( [Total quantity no fob] )
        )

EVALUATE
ROW (
    "total rows", COUNTROWS ( T1 ),
    "total customers", COUNTROWS ( SUMMARIZE ( T1, [Customer number] ) )
)

    which should give the same number for both total rows and total customers.

13 Replies

  • johnt75's avatar
    johnt75
    Super User
    3 years ago

    Try

    Table1 =
ADDCOLUMNS (
    ALLNOBLANKROW ( 'Query1'[Customer number] ),
    "Customer type", IF ( CALCULATE ( [Total quantity no fob] ) >= 10000, "Top", "No top" ),
    "Total sales", CALCULATE ( [Total sales no fob] ),
    "Total quantity", CALCULATE ( [Total quantity no fob] )
)
      • johnt75's avatar
        johnt75
        Super User
        3 years ago

        Try it as just

        Table1 = ALLNOBLANKROW ( 'Query1'[Customer number] )

        and then see if you can create the relationship. It is not clear to me whether the problem lies in the customer number column or in one of the other columns.