Forum Discussion

VinayMedishety's avatar
VinayMedishety
Frequent Visitor
3 years ago

The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

Trying to Convert this SQL Code into DAx and it gives me "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."
SQl Code - SELECT s.Department,SUM(s.[Headcount Actuals]),s.[Year Period]
FROM(
SELECT g.[TLC Code],g.[Year Period],g.Year,g.Period,g.[FLSA Type],g.Department,g.[CostCenter Code],g.[Job Family],g.[Function],g.[Expense Type],
g.[Job Codes], SUM(DISTINCT g.[Headcount Actuals])[Headcount Actuals]
  FROM TDWUMVP.edw.vw_fact_fin_employee_headcount_plans_account g WITH(NOLOCK)
  WHERE g.[Expense Type] = 'SG&A'
--and g.Department='100 - Operations HUB'
GROUP BY g.[TLC Code],g.[Year Period],g.Year,g.Period,g.[FLSA Type],g.Department,g.[CostCenter Code],g.[Job Family],g.[Function],g.[Expense Type],
g.[Job Codes],g.[Year Period]
)s
GROUP BY s.Department,s.[Year Period]

DAX - 

SUMMARIZE( FILTER( 'Fact Employee', 'Fact Employee'[Expense Type] = "SG&A" ), 'Fact Employee'[Department], 'Fact Employee'[Year Period], "Headcount Actuals", SUMX(DISTINCT('Fact Employee'[Headcount Actuals]), 'Fact Employee'[Headcount Actuals]) )
Need Help
Tips and Tricks

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    HI VinayMedishety,

    What type of data source are you worked? If you are working with SQL , oracle data source, you can try to use these queries in data connector SQL statements. (you can find it in advanced option -> SQL statement)

    BTW, these dax formula will return a table, you need use in calculated table expression instead of calculated column or measure.

    Regards,

    Xiaoxin Sheng

Related content