Need help with DAX formula to mark the shape

[vanhell54321]

Dear power BI community,

I have the following columns I need to make a new calculated column using DAX formula. 

The following data looks like the below

Nation	Fruits
1	Banana
1	Orange
2	Banana
3	Orange
4	Banana
4	Orange

I need to create a new column shape that marks orange as circle and Banana as long and if the Nation as both the fruits the column marks it as both. 

Nation	Fruits	Shape
1	Banana	Both
1	Orange	Both
2	Banana	long
3	Orange	circle
4	Banana	Both
4	Orange	Both

Thanks!

Regards,

Van

[Anonymous]

Hi @vanhell54321 ,

Use the below Dax formula:

Shape =
VAR CountFruits =
    CALCULATE ( COUNT ( table[fruit] ), ALLEXCEPT ( table, table[nation] ) )
RETURN
    SWITCH (
        TRUE (),
        CountFruits = 2, IF ( table[fruit] = "orange", "circle", "long" ),
        "both"
    )

Regards,

Gaurav Raj Singh 

LinkedIN : https://www.linkedin.com/in/gauravrajsingh/

[Anonymous]

Try something like this

I am not sure this will work or not but give try

Column=
Var check=Calculate(distinctcount(table[fruit]),allexcept(table,table[nation]))
Return
Switch(true(),check=2,
If(table[fruit]="orange","circle","long"),"both")

Thanks,
Pravin

If it resolves your problem mark it as a solution and give Kudos.