Need to take sum of salary. attached data model

[NILU090]

Hi Team,

Inshort, I want to take sum of salary (from Fact table) vs State(from Code table).

Manager Hierarchy R_ID to Fact Table R_ID inactive relationship.

[Anonymous]

Hi,

Thanks for the solution Gabry provided, and i want to offer some more infotmation for user to refer to.

hello @NILU090 , you can try the following meaure.

MEASURE =
CALCULATE (
    SUM ( Fact_Table[Salary] ),
    USERELATIONSHIP ( Fact_Table[R_ID], ManagerHierachy[R_ID] ),
    CROSSFILTER ( Groups[CODE], ManagerHierachy[CODE], BOTH )
)

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.

[NILU090]

code and manager hierarchy doesnt have any direct , indirect relationship

[Gabry]

Yeah I see, but the key "CODE" is the same, so you can build a relationship between these tables. So if you set a relationship, will it be 1:1 or what type?

[NILU090]

we cant modified the relationship. This is what we have. on basis of that need to write dax.

[Gabry]

Ok. In my opinion you can't do that just with DAX without make any modification to the model but wait for the help of experts 😉

[Gabry]

Hello @NILU090 ,

not sure but try this

Measure = calculate(sum(''Fact table'[Salary]), userelationship('Fact table'[R_ID], 'Manager hierarchy'[R_ID]))

let me know

[NILU090]

Its not working.

Manager hierarchy filters to groups ( Manager hierarchy 1 side : groups many side)

[Gabry]

Oh sorry you're right. So wich is the relationship between code and manager hierarchy?