Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Problem with SWITCH formula

Hello

 

I try to write a SWITCH formula but it does not offer to autocomplete the table and column of my table when I enter the first parameter in the formula. It says:

A single value for column 'ID' in table 'Products' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Why does this happen?

 

Secondly, I want to write a CALCULATE formula were:

CALCULATE(COUNT(DifferentTable[Col1]),DifferentTable[Col2]=SameTable[Col1])

 

I tried to write:

CALCULATE(COUNT(DifferentTable[Col1]),DifferentTable[Col2]=RELATED(SameTable[Col1]))

Do you think that will work? I have not been able to test yet due to the first error.

 

Thanks

 

Need Help

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous , the formula you may have try with filter CALCULATE(COUNT(DifferentTable[Col1]),filter(DifferentTable,DifferentTable[Col2]=RELATED(SameTable[Col1])))

     

    If switch is ised in a measure you have use insure single value like in given example

    Switch( True(),
    Max(Table[column]) = "19/20-P13", [Overdue]
    Max(Table[column]) = "20/21-P1" ,[Overdue]*0.9
    Max(Table[column]) = "20/21-P2" ,[Overdue]*0.8
    Max(Table[column]) = "20/21-P3" ,[Overdue]*0.7
    Max(Table[column]) = "20/21-P4" ,[Overdue]*0.6
    Max(Table[column]) = "20/21-P5" ,[Overdue]*0.5
    Max(Table[column]) = "20/21-P6" ,[Overdue]*0.4
    )

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      amitchandak 

       

      Thanks but I get this error:

      The column 'SameTable[Col1]' either doesn't exist or doesn't have a relationship to any table available in the current context.

       

      Any solution? 

      • amitchandak's avatar
        amitchandak
        Super User
        6 years ago

        Anonymous , it will work from 1-M direction. So change filter Table and related column

        Try like

        CALCULATE(COUNT(DifferentTable[Col1]),filter(SameTable,SameTable[Col1]=RELATED(DifferentTable[Col2])))

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    Hi, Anonymous 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

     

    DifferentTable:

     

    SameTable:

     

    You may create a measure or a calculated column as below.

    Calculated column:
Result Column = 
var _col1 = [Col1]
return
CALCULATE(
    DISTINCTCOUNT(DifferentTable[Col1]),
    FILTER(
        DifferentTable,
        DifferentTable[Col2] = _col1
    )
)

Measure:
Result Column = 
var _col1 = [Col1]
return
CALCULATE(
    DISTINCTCOUNT(DifferentTable[Col1]),
    FILTER(
        DifferentTable,
        DifferentTable[Col2] = _col1
    )
)

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.