Forum Discussion

mshaikh's avatar
mshaikh
Regular Visitor
7 years ago
Solved

MS - Measure Conditional formula

Hi,   I am looking for Conditional measure formula using 2 tables. Following sample file is attached for reference.   Can someone please provide suitable measure formula in order to calculate exp...
General Comment
  • v-piga-msft's avatar
    v-piga-msft
    7 years ago

    Hi mshaikh,

     

    Firstly, you could create the relatioship with the table A and B with Key column.

     

    Then you could create the calculated column for Table-B with the formula below.

     

    Column =
IF (
    CALCULATE ( MAX ( 'Table-A'[A01-KEY] ) )
        = CALCULATE ( MAX ( 'Table-B'[B01-KEY] ) )
        && CALCULATE ( MAX ( 'Table-B'[B01-Type] ) ) = 99,
    CALCULATE (
        SUM ( 'Table-A'[A01-Amount] ),
        FILTER ( 'Table-B', 'Table-B'[B01-KEY] = EARLIER ( 'Table-B'[B01-KEY] ) )
    ),
    0
)

    Then you will get the output you like.

     

     

     

    If you still need help, please share your desired output so that we could help further on it.

     

    Best  Regards,

    Cherry

v-piga-msft's avatar
v-piga-msft
Resident Rockstar
7 years ago

Hi mshaikh,

 

Firstly, you could create the relatioship with the table A and B with Key column.

 

Then you could create the calculated column for Table-B with the formula below.

 

Column =
IF (
    CALCULATE ( MAX ( 'Table-A'[A01-KEY] ) )
        = CALCULATE ( MAX ( 'Table-B'[B01-KEY] ) )
        && CALCULATE ( MAX ( 'Table-B'[B01-Type] ) ) = 99,
    CALCULATE (
        SUM ( 'Table-A'[A01-Amount] ),
        FILTER ( 'Table-B', 'Table-B'[B01-KEY] = EARLIER ( 'Table-B'[B01-KEY] ) )
    ),
    0
)

Then you will get the output you like.

 

 

 

If you still need help, please share your desired output so that we could help further on it.

 

Best  Regards,

Cherry