Measure with 2 table involved

[ACN_Teo_Garro]

We have 2 table:

1) CRUSCOTTO: NAME, PRELIEVI_MULTI, RUOLO_MASTER

2) REGOLE_OMOG: RUOLO_MASTER, SOGLIA

We must create a new measure that create a sum but with some filter joined between the 2 table above (as the following sql extraction):

SELECT sum(A.PRELIEVI_MULTI)

from CRUSCOTTO A

inner join REGOLE_OMOG B on (A.RUOLO_MASTER=B.RUOLO_MASTER)

where A.PRELIEVI_MULTI >= B.SOGLIE

We try to do this but it doesn't work:

test sum prel omog = CALCULATE(SUMX(CRUSCOTTO,CRUSCOTTO[PRELIEVI_MULTI]),FILTER(CRUSCOTTO,CRUSCOTTO[RUOLO_MASTER]=SELECTEDVALUE(REGOLE_OMOG[RUOLO_MASTER]) && CRUSCOTTO[PRELIEVI_MULTI]>=SELECTEDVALUE(REGOLE_OMOG[A_N])))

Can someone help us?

Thank you

[Anonymous · Accepted solution]

Hi @ACN_Teo_Garro 

You can try the following Measure.

test sum prel omog =
VAR a =
    CALCULATE (
        MAX ( REGOLE_OMOG[SOGLIA] ),
        FILTER (
            REGOLE_OMOG,
            [RUOLO_MASTER] = SELECTEDVALUE ( REGOLE_OMOG[RUOLO_MASTER] )
        )
    )
RETURN
    CALCULATE (
        SUM ( CRUSCOTTO[PRELIEVI_MULTI] ),
        FILTER (
            CRUSCOTTO,
            [RUOLO_MASTER]
                IN VALUES ( REGOLE_OMOG[RUOLO_MASTER] )
                    && [ PRELIEVI_MULTI] >= a
        )
    )

And can you provide some sample data?

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.

[ACN_Teo_Garro]

Hi Yolo Zhu,

thanks for your feedback.

The Measure that you provide us works fine and apply the filter that we need.

But, as you can see in the picture below

we have

- in the left side (test GREG) the measure with your filter applied

- in the right side (RI_AR11_PREL_ANOM)  the measure without filter

the data in the column are correct (i.e. in the first row is correct that without filter the value is 3196 and with filters applied become 2177) BUT the sum value at the and of the column in INCORRECT for the filtered column (test GREG). It doesn't take the filter in it.

Could you explain why?

Can someone help us?

Regards