Column in SUMMARIZE function was not found in the input table

[admin_xlsior]

Hi guys,

I'm a newbie and just learning DAX. I was trying to create a table with SUMMARIZE function but has an error which I think it is weird. I have the column but the function keep telling me it was not found.

This is my code:

Summary Table using SUMMARIZE =

          CALCULATETABLE(

                              SUMMARIZE('Fact Sale',

                                                   'Dimension Date'[Calendar Month Label],

                                                   'Dimension City'[State Province],

                                                   "Sum of Qty", SUM('Fact Sale'[Quantity])

                                                   )

                                                   , USERELATIONSHIP( 'Fact Sale'[Invoice Date Key], 'Dimension Date'[Date] )

                                                   )

and my data relationship is :

As you can see, I have all the fields / columns, what did I missed or incorrectly wrote ?

Thanks,

[michaela2445]

Try changing the column types both to text (instead of whole number for what i assume city key is). Even if it is set not to sum, the field is being looked at as numerical and trying to sum it when bringing in data.

[JaclynPugh2022]

The result I'm looking for is the last vendor purchased from.  What is the problem with this formula?  It looks like it should work...then I have this error:

Last Supplier Purchased From
=MAXX(
TOPN(1,
SUMMARIZE('DimDate1','DimDate1'[Date],'received pivot'[Vendor Name]),
CALCULATE( MAX('DimDate1'[Date])),ASC),
'received pivot'[Vendor Name])

[edhans]

1. You don't need USERELATIONSHIP(). That is only used to activate inactive relationships, which are shown in the model with dashed lines vs solid lines. Your image of your model has no dashed lines.
2. You don't need CALCULATETABLE as SUMMARIZE() returns a table, and CALCULATETABLE() is would be used for additional filters, but you don't have any. (You don't need USERELATIONSHIP() per 1 above)

As to why it thinks your State Province column isn't there, I am not sure. I cannot get this to fail. I tried this in one of my reports and the table created just fine. I am wondering if there is some syntax issue I cannot see in the formula you have shown.

TestTable = 
SUMMARIZE(
    Sales,
    Items[Item Family],
    Customers[Customer Type],
    'Calendar'[Month Name],
    "New Sales Amount",SUM(Sales[Sales])
)

My data model looks like this which is similar to yours. 

[admin_xlsior]

Hi thanks,

For the "USERELATIONSHIP", you're right. there's a flaw on the relationship design, since I tried to recreated it, and it supposed to have another relationship for the other key in my Fact Sale. 

The syntax, however, I recreate from the book which I used to learn, "Beginning DAX with PowerBI" By Phillip Seamark, Chapter 4 Page 73. Wondering if you have this book as well ?

This is the exact screenshot of the syntax in that book:

Thanks,

[v-lili6-msft]

hi, @admin_xlsior 

I don't have this book "Beginning DAX with PowerBI", but if you could share your sample pbix file.

For "USERELATIONSHIP", i think there must be more than one relationship are created between "Fact sales" with "Dimension date" based on the example. so for the conditional "USERELATIONSHIP", it needs CALCULATETABLE to evaluates a table expression in a context modified by the given filters.

Best Regards,

Lin

[admin_xlsior]

Hi Lin,

I was about to send you the pbix, however since it is Direct Query then I tried to change to Import for your convinience. Unexpectedly the error is vanished. Is the error because a limitation of Direct Query ?

Another thing, yes for the USERELATIONSHIP (and CALCULATETABLE) is cleared, thank you very much on your kind explanation.

Thanks,

[edhans · Accepted solution]

---

@admin_xlsior wrote:

Hi Lin,

I was about to send you the pbix, however since it is Direct Query then I tried to change to Import for your convinience. Unexpectedly the error is vanished. Is the error because a limitation of Direct Query ?

Another thing, yes for the USERELATIONSHIP (and CALCULATETABLE) is cleared, thank you very much on your kind explanation.

Thanks,

---

I don't have the book either, and Phil's syntax is correct, but I don't know the context of his example. If you had the problem with direct query but not import, then yes, that could be the fix. Direct Query doesn't support all Power BI features and should generally be used sparingly. Importing is where Power BI really shines.