Distinct error

[Analitika]

I have table1[name] 

and Mesuare=

var sum1 = [mesuare1] - [measure2]

var sum2 = [mesuare3] - [measure4]

RETURN

SWITCH(TRUE(),
DISTINCT(Table1[name]) = "Name1", sum1,
DISTINCT(Table1[name]) = "Name2", sum2
)

It work wel while table1[name]  has no dublicates, if table1[name] have dublicate name measuare not work, why?

MdxScript(Model) (253, 43) Calculation error in measure '[Measure]: A table of multiple values was supplied where a single value was expected.

[v-alq-msft · Accepted solution]

Hi, @Analitika 

Distinct function returns a one-column table that contains the distinct values from the specified column. In other words, duplicate values are removed and only unique values are returned.

You may try the following measure to see if it works.

Mesuare =
VAR sum1 = [mesuare1] - [measure2]
VAR sum2 = [mesuare3] - [measure4]
RETURN
    SWITCH (
        TRUE (),
        "Name1" IN DISTINCT ( Table1[name] ), sum1,
        "Name2" IN DISTINCT ( Table1[name] ), sum2,
        BLANK ()
    )

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]

@Analitika , try

Measure=
var sum1 = [mesuare1] - [measure2]
var sum2 = [mesuare3] - [measure4]

RETURN
SWITCH(TRUE(),
max(Table1[name]) = "Name1", sum1,
max(Table1[name]) = "Name2", sum2
)

[Analitika]

But with MAX() not work if table has no dublicates 😫

[az38]

@Analitika 

you should define cell for compare more accurate. like

Mesuare=

var sum1 = [mesuare1] - [measure2]

var sum2 = [mesuare3] - [measure4]

var currentName = MAX(Table1[name])

RETURN

SWITCH(TRUE(),
currentName  = "Name1", sum1,
currentName  = "Name2", sum2,
BLANK()
)