Circular Dependencies - Calculated Table

[emarome94]

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

[johnt75]

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] )
)

[emarome94]

Hi @johnt75 ,

It gives the same problem

E

[johnt75]

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.

[emarome94]

@johnt75 johnt75 This work but it gives a many to many relationship. 

The problem is in customer numbers as it retrieves an empty row in addition to the other correct 721 unique values. 

If in Power Query I remove the blank rows the relationship work well but I would like to not do that cause cause I need that blank rows in the model: so it's about why ALLNOBLANKROW does not remove that blank values

E

[johnt75]

Try using DISTINCT in my original code instead of ALLNOBLANKROW

[emarome94]

@johnt75 
Same result as of ALLNOBLANKROW

[johnt75]

are you sure that the empty value is actually blank and doesn't have a space or some other character in it ?

[emarome94]

@johnt75 

I checked the column with an ISBLANK and in fact the "empty" rows are returned as false.

I've already tried to managed the "empty" rows in power query with replace values " " with "" but no success. 

So I think the problem is that and this answer to the topic that I should potentially close, but let me know if you are okay in giving me some tips for handling values like this in PowerQuery

E 

[johnt75]

Instead of replacing " " with "", replace it with null. You can type null into the "replace with" box.

[emarome94]

I have managed to make the empty cells as blank values (therefore isblank columns actually give true for that cells) but the result remains the same in terms of relationship etc. 

[johnt75]

Can you create the relationship if you just create the table as DISTINCT('Query1'[Customer number]), without the additional calculated columns ?

[emarome94]

Yes but only if I filter in PowerQuery the blank values. I mean I understand that those are the problem and I removed them from now

[johnt75 · Accepted solution]

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.