Problem with SWITCH formula

[Anonymous]

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

[v-alq-msft]

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.

[amitchandak]

@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]

@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]

@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])))