Forum Discussion

ACN_Teo_Garro's avatar
ACN_Teo_Garro
New Member
3 years ago
Solved

Measure with 2 table involved

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

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      ACN_Teo_Garro
      New Member
      3 years ago

      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