topic Re: Creating table with 2 columns from different tables in DAX Commands and Tips

Creating table with 2 columns from different tables

Anonymous — Tue, 19 May 2020 19:28:13 GMT

I currently have 2 tables "Company Supplier" and "Intake Data"

I'm trying to use summarize function to create a table of values with only

Company Supplier Name from the "Company Supplier Table"

Company Id from the "Intake Data" table

I've tried this:

Table = SUMMARIZE('Company_Supplier'[Company_Supplier_Name])

+SUMMARIZE('looker_views lkr_Intake_Data',[Company_Id])

Yet I encounter the error:The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

Can anyone help?

Anonymous — Wed, 20 May 2020 00:30:35 GMT

What is the relationship between the tables?

Best
D

Anonymous — Wed, 20 May 2020 08:47:05 GMT

The relationship is Intake Data* ----- 1Supplier Data (Many to 1) based on the "Company Supplier id"

I've created a table with:

Table = var CompanySupplierName = MAX('Intake_Data'[Company_Id])

return

SUMMARIZE(FILTER('Intake_Data','Intake_Data'[Company_Id] = CompanySupplierName),

'Intake_Data'[Company Supplier Name],"Count Company Id", CompanySupplierName)

However it's giving me the overall max value in the column:

Is there a way to alter the formula to get count of company id's per supplier?