Calculated measure with dynamic formula coming from dataset

[fabiuzz]

Hi all,

I have to build a measure made with a formula coming from the DB.

Maybe I can solve it in the ETL, but the number of record will increase too much, cause every filter in presentation-time on other dimension/attributes should impact on the calculated formula. So I'm tryng to bring the formula logic into the data-model.

e.g. 

DataType | Value

A | 100

A | 200

B | 300

B | 200

C | 300

X | 0,34

Y | 2,77

F1 | A+B-C

F2 | (A + B) * X - (C * Y)

F3 | ((A * X) + (B * Y) - (C * B)) / C

No prob when there are only + or - in the formula as in the case of F1 formula: SUMX (Value * Sign)... where sign is "1" or "-1".

In case of F2 and F3 it's more difficult, cause I have bracket and multiplication, and the lenght/structure is not given. Some idea on how can I solve that?

Thanks

Fabio

[fabiuzz]

Hi Majidbhatti, unluckily no update.

[Majidbhatti]

I have similar ETL requirement. @fabiuzz any update?

[saini-harish]

Hi, have you got solution for this problem? I would like to think that this can be solved using "M"? I've similar problem but don't wish to reinvent the wheel

Cheers,

Harish

[fabiuzz]

Hi Harish,

actually I haven't found a way to do this with DAX formula.

Maybe in "M" you can do something, but I think you can only reach a part of the solution. My goal was not only to calculate row by row value, but also to apply the formula in the aggregation level that should not be the simple sum (or the average, or the count, or whatever else...). Obviously all affected by the filters I use in the reporting phase.

Bye!

Fabio

[v-shex-msft]

HI @fabiuzz,

AFAIK, power bi not suitable for calculate with specific cell value, it not contains column index and row index.
So you had to manually look up specific records and summary them to calculate.

Sample measure:

F1 =
VAR A =
    CALCULATE (
        SUM ( table[Value] ),
        FILTER ( ALL ( Table ), Table[Data Type] = "A" )
    )
VAR B =
    CALCULATE (
        SUM ( table[Value] ),
        FILTER ( ALL ( Table ), Table[Data Type] = "B" )
    )
VAR C =
    CALCULATE (
        SUM ( table[Value] ),
        FILTER ( ALL ( Table ), Table[Data Type] = "C" )
    )
VAR X =
    CALCULATE (
        SUM ( table[Value] ),
        FILTER ( ALL ( Table ), Table[Data Type] = "X" )
    )
VAR Y =
    CALCULATE (
        SUM ( table[Value] ),
        FILTER ( ALL ( Table ), Table[Data Type] = "Y" )
    )
RETURN
    A + B
        - C

F2
    =
    VAR A =
        CALCULATE (
            SUM ( table[Value] ),
            FILTER ( ALL ( Table ), Table[Data Type] = "A" )
        )
    VAR B =
        CALCULATE (
            SUM ( table[Value] ),
            FILTER ( ALL ( Table ), Table[Data Type] = "B" )
        )
    VAR C =
        CALCULATE (
            SUM ( table[Value] ),
            FILTER ( ALL ( Table ), Table[Data Type] = "C" )
        )
    VAR X =
        CALCULATE (
            SUM ( table[Value] ),
            FILTER ( ALL ( Table ), Table[Data Type] = "X" )
        )
    VAR Y =
        CALCULATE (
            SUM ( table[Value] ),
            FILTER ( ALL ( Table ), Table[Data Type] = "Y" )
        )
    RETURN
        ( A + B )
            * X
            - ( C * Y )

F3
    =
    VAR A =
        CALCULATE (
            SUM ( table[Value] ),
            FILTER ( ALL ( Table ), Table[Data Type] = "A" )
        )
    VAR B =
        CALCULATE (
            SUM ( table[Value] ),
            FILTER ( ALL ( Table ), Table[Data Type] = "B" )
        )
    VAR C =
        CALCULATE (
            SUM ( table[Value] ),
            FILTER ( ALL ( Table ), Table[Data Type] = "C" )
        )
    VAR X =
        CALCULATE (
            SUM ( table[Value] ),
            FILTER ( ALL ( Table ), Table[Data Type] = "X" )
        )
    VAR Y =
        CALCULATE (
            SUM ( table[Value] ),
            FILTER ( ALL ( Table ), Table[Data Type] = "Y" )
        )
    RETURN
        ( ( A * X )
            + ( B * Y )
            - ( C * B ) )
            / C

Regards,

Xiaoxin Sheng

[fabiuzz]

Ok, this work, but the matter is that I want to autogenerate (or autointerpretate) the formula u write manually, starting from the composition of the formula stored in table inside the dataset.

In other words the formula F1,F2,F3,... should be evalueted from the dataset: something like convertstringtoformula...

[v-shex-msft]

Hi @fabiuzz,

As I said, power bi not contains column index and row index so you need to manually lookup for specific value.

I think it is impossible to use dax to achieve.

Regards,

Xiaoxin Sheng