Forum Discussion
Circular Dependencies - Calculated Table
- 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.
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.
- emarome943 years ago
Helper I
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