Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Issue with New Colimn query

I am trying to enter a filter query to select a part of a table I have. The query however is returning the error - The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

 

The table is called DataTable1 and looks like 

indicator    respondent group      score

planning

General average

1

planning

SC Director

3

planning

CPO

5

planning

Commercial Director

7

work

General average

2

work

SC Director

6

work

CPO

2

work

Commercial Director

4

Procure

General average

6

Procure

SC Director

3

Procure

CPO

7

Procure

Commercial Director

3

Ta

General average

4

Ta

SC Director

1.5

Ta

CPO

0

Ta

Commercial Director

2

Customs

General average

1

Customs

SC Director

3

Customs

CPO

0

Customs

Commercial Director

5

 

The query im using (which i have used in both a new column and a new measure) is:

 

GenAvg = calculate(filter(DataTable1, DataTable1[respondent group] = "General average"))

 

I have also tried:

 

GenAvg = filter(DataTable1, DataTable1[respondent group] = "General average")

 

however both queries return the same error message. Im not sure whats wrong with it as it is only referring to one column.

 

Need Help

4 Replies

  • tex628's avatar
    tex628
    Community Champion
    6 years ago

    You're not applying any aggregation to your calculaton. It looks like you want to do some calculation on the column score while filtering respondent group to only general average. What type of calculation are you going for?

    Br,
    J

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi tex,

       

      I am trying to select the results for just one set of the respondent groups so that i can have mutiple value sets on the y axis from the same table for a radar chart.

       

      Thanks

      Chris

      • tex628's avatar
        tex628
        Community Champion
        6 years ago

        Alright, 

        In that case you should try:

        GenAvg = Calculate(Selectedvalue(DataTable1[Score]), Filter(DataTable1, DataTable1[respondent group] = "General average"))


        The DAX syntax CALCULATE() requires an expression, this can be any type of aggregation. In the instance above im using SELECTEDVALUE(), in simple terms it checks if there is only one value available in the column at the time of the calculation and then returns that value.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous,

    Calculate function not able to return table, you need to apply aggregate on your results to keep only return one column(calculated column) or one value(measure)

    What aggregate method you want to apply on your formula if multiple records suitable for your conditions?
    In addition, what type of calculated fields did you want? if you want its result can be dynamic filter by filter/slicer, please use measure formula.

    Regards,

    Xiaoxin Sheng