Forum Discussion

Nick_2510's avatar
Nick_2510
Helper I
3 years ago

CrossJoin and IF statement HELP!

Help please
А need calculate if statement 
 
if([av_21] > 0, [OOS_2]*[av_21],
    [OOS_2] * (SUM('Data'[Sale]) / [Total NumberDays])
 
using CROSSJOIN
 
I am trying these measure
OOS, L = CROSSJOIN ( VALUES ('Calendar'[Date]), VALUES ( 'Data'[Glossary.Name_SKU]) ), (if([av_21] > 0, [OOS_2]*[av_21],
    [OOS_2] * (SUM('Data'[Sale]) / [Total NumberDays])))
 
but it doesnt work
Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi  Nick_2510 

    The crossjoin() function will return a table, so you cannot directly use it in measure. you can refer to the following example.

    OOS, L =
VAR a =
    ADDCOLUMNS (
        CROSSJOIN ( VALUES ( 'Calendar'[Date] ), VALUES ( 'Data'[Glossary.Name_SKU] ) ),
        "TEST",
            (
                IF (
                    [av_21] > 0,
                    [OOS_2] * [av_21],
                    [OOS_2]
                        * ( SUM ( 'Data'[Sale] ) / [Total NumberDays] )
                )
            )
    )
RETURN
    MAXX (
        FILTER (
            a,
            [Date] = SELECTEDVALUE ( 'Calendar'[Date] )
                && [Glossary.Name_SKU] = SELECTEDVALUE ( 'Data'[Glossary.Name_SKU] )
        ),
        [TEST]
    )

    Best Regards!

    Yolo Zhu

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