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

Hi capko 
can you show tha calculation for the Value (Source AB)?
If i undestrand well it will be the avg of 18,85, 10,17 and 19,00. The valid line for item 1 source AB and the result will be 16,01.
I forgot something?
Why you are show 14,59?
Thank you

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

Hi ! You're completely right, it has been an error on my side 🙂