PowerBI multiple If's doesn't split data

0
votes

I'm currently looking to make an age slicer on PowerBI but it doesn't work. Could you help me ? Thank you in advance ! my code :

Age Slice.1 = 
    if(
        20<VALUE(CONTRACT_BASIS[Age]),
        "<20",
        if(
            20<=VALUE(CONTRACT_BASIS[Age])<25,
            "20-25",
            if(
                25<=VALUE(CONTRACT_BASIS[Age])<30,
                "25-30",
                if(
                    30<=VALUE(CONTRACT_BASIS[Age])<40,
                    "30-40",
                    if(
                        40<=VALUE(CONTRACT_BASIS[Age])<50,
                        "40-50",
                        if(
                            50<=VALUE(CONTRACT_BASIS[Age]),
                            "50+",
                            "no age displayed"
                            )              
                    )
                )
            )
        )
    )

the message displayed : "DAX comparison operations do not support comparing values of type True/False with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values."

2
if-statementpowerbi

2 Answers

0
votes
Age Slice.1 = 
    if(
        CONTRACT_BASIS[Age]<20,"<20",
        if(
            20<=CONTRACT_BASIS[Age] && CONTRACT_BASIS[Age]<25,"20-25",
            if(
                25<=CONTRACT_BASIS[Age] && CONTRACT_BASIS[Age]<30,"25-30",
                if(
                    30<=CONTRACT_BASIS[Age] && CONTRACT_BASIS[Age]<40,"30-40",
                    if(
                        40<=CONTRACT_BASIS[Age] && CONTRACT_BASIS[Age]<50,"40-50",
                        if(
                            50<=CONTRACT_BASIS[Age],"50+"
                            )              
                    )
                )
            )
        )
    )

is also correct

0
votes

Solved it with this query:

Age Slice.1 = 
SWITCH(
    TRUE(),
    CONTRACT_BASIS[Age]<20,"<20",
    20<=CONTRACT_BASIS[Age] && CONTRACT_BASIS[Age]<25,"20-25",
    25<=CONTRACT_BASIS[Age] && CONTRACT_BASIS[Age]<30,"25-30",
    30<=CONTRACT_BASIS[Age] && CONTRACT_BASIS[Age]<40,"30-40",
    40<=CONTRACT_BASIS[Age] && CONTRACT_BASIS[Age]<50,"40-50",
    50<=CONTRACT_BASIS[Age],"50+"
)