Forum Discussion

capko's avatar
capko
Icon for Helper II rankHelper II
2 years ago
Solved

Complex problem with several criteria using PBI / DAX

Hello,   I've an interesting problem to solve but I need some in order to implement it in PBI with DAX.   I've the following table with several items. These items will always have a price, a curr...
Need Help
  • lucadelicio's avatar
    lucadelicio
    2 years ago

    Try

    Measure =
    var _a =
    CALCULATE(
        AVERAGE(Foglio1[    Value USD    ])
        ,Foglio1[Valid Date] = 1
        ,Foglio1[    Code    ] <> "A"
    )

    var _b =
    CALCULATE(
        AVERAGE(Foglio1[    Value USD    ])
        ,Foglio1[    Code    ] <> "A"
    )

    var _c =
    CALCULATE(
        AVERAGE(Foglio1[    Value USD    ])
    )

    RETURN
    IF(ISBLANK(_a) && ISBLANK(_b), _c
        ,IF(ISBLANK(_a), _b
            ,_a
        )
    )


    Second measure for the text:
    Measure 2 =
    var _a =
    CALCULATE(
        AVERAGE(Foglio1[    Value USD    ])
        ,Foglio1[Valid Date] = 1
        ,Foglio1[    Code    ] <> "A"
    )

    var _b =
    CALCULATE(
        AVERAGE(Foglio1[    Value USD    ])
        ,Foglio1[    Code    ] <> "A"
    )

    var _c =
    CALCULATE(
        AVERAGE(Foglio1[    Value USD    ])
    )

    RETURN
    IF(ISBLANK(_a) && ISBLANK(_b), "Code A"
        ,IF(ISBLANK(_a), "Out of dates"
            , "Yes"
        )
    )

    And put both into a matrix:
    Row item
    Columns: Source



lucadelicio's avatar
lucadelicio
Icon for Impactful Individual rankImpactful Individual
2 years ago

Try

Measure =
var _a =
CALCULATE(
    AVERAGE(Foglio1[    Value USD    ])
    ,Foglio1[Valid Date] = 1
    ,Foglio1[    Code    ] <> "A"
)

var _b =
CALCULATE(
    AVERAGE(Foglio1[    Value USD    ])
    ,Foglio1[    Code    ] <> "A"
)

var _c =
CALCULATE(
    AVERAGE(Foglio1[    Value USD    ])
)

RETURN
IF(ISBLANK(_a) && ISBLANK(_b), _c
    ,IF(ISBLANK(_a), _b
        ,_a
    )
)


Second measure for the text:
Measure 2 =
var _a =
CALCULATE(
    AVERAGE(Foglio1[    Value USD    ])
    ,Foglio1[Valid Date] = 1
    ,Foglio1[    Code    ] <> "A"
)

var _b =
CALCULATE(
    AVERAGE(Foglio1[    Value USD    ])
    ,Foglio1[    Code    ] <> "A"
)

var _c =
CALCULATE(
    AVERAGE(Foglio1[    Value USD    ])
)

RETURN
IF(ISBLANK(_a) && ISBLANK(_b), "Code A"
    ,IF(ISBLANK(_a), "Out of dates"
        , "Yes"
    )
)

And put both into a matrix:
Row item
Columns: Source