Filter a table with list of selected values from other tables

[lboldrino]

Hi.

I need to calculate sum of effort per employee over booking-element

But for example, the csv-101 have 3 booking elements, I need to filter my table_2 by these 3 elements.

I must find which booking-element has any cvb, and calculate efforts over this booking-element (booking ober this selected booking-element for all cvbs)

Any idea??

viewcvb:

cvb	booking_element
cvb-101	CPSI599-1
cvb-101	CPSI599-12
cvb-101	CPSI600-1
cvb-102	CPSI511
cvb-102
cvb-103	CPSI500
cvb-103	CPSI500-1
cvb-104	CPSI505
cvb-105	CPSI600-1

view effort:

employee	effort	booking_element
em1	3,2	CPSI599-1
em2	3	CPSI599-1
em3	1	CPSI599-12
em4	0,6	CPSI600-1
em4	2	CPSI511
em7	5,5	CPSI500-1
em10	1	CPSI500
em8	0,13	CPSI599-12
em9	1	CPSI600-1

[lboldrino]

as this. but this dosnt work:

Viewbooking_project =

var list_booking_ids = VALUES(Viewsprintreview[dk_booking_element_key])

return

filter(Viewefforts, Viewefforts[dk_ProjectElementID] in {list_booking_ids })

[BA_Pete]

Hi @lboldrino ,

Maybe use a [booking_element] bridge table?

- Create a distinct table of all possible [booking_element] values (bridge)

- Relate CVB to BRIDGE, Many to One, filter direction Both.

- Relate BRIDGE to EFFORT, One to Many, filter direction Single.

Use CVB[cvb] in visuals to filter/aggregate EFFORT values.

Pete

[lboldrino]

anyone who can help on this topic? Thank you very much!

if there is anythin unclear, please let me know.

i try this measure, but its not  correct:

effort_booking = 
var _selectedids = CALCULATETABLE(VALUES(Viewsprintreview[dk_booking_element_key]), FILTER(Viewsprintreview, Viewsprintreview[cvb_key] = [selectedcvb]))

var _tbl = DISTINCT(filter(SUMMARIZE(Viewefforts,Viewefforts[dk_ProjectElementID],Viewefforts[effort]),Viewefforts[dk_ProjectElementID] in {_selectedids})) 
Return 
sumx(_tbl,[effort])<p> </p>

[v-deddai1-msft · Accepted solution]

Hi @lboldrino ,

Please use the following measure:

effort_booking =
VAR _selectedids =
    CALCULATETABLE (
        VALUES ( Viewsprintreview[dk_booking_element_key] ),
        FILTER ( Viewsprintreview, Viewsprintreview[cvb_key] = [selectedcvb] )
    )
VAR _tbl =
    SUMMARIZE (
        Viewefforts,
        Viewefforts[dk_ProjectElementID],
        "effort",
            CALCULATE (
                SUM ( Viewefforts[effort] ),
                FILTER ( Viewefforts, Viewefforts[dk_ProjectElementID] IN _selectedids )
            )
    )
RETURN
    SUMX ( _tbl, [effort] )

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[lboldrino]

not cvb, efforts ist over booking elements. a booking elements can assign to more cvbs .

[amitchandak]

@lboldrino , a new measure like below, You need to add any additional filter need

measure =

var _1  = allselected(viewcvb[booking_element])

return 

calculate(countrows(vieweffort), filter(vieweffort, vieweffort[booking_element] in _1))

see if the above can help

[marcussimple24]

It works perfectly...thx a lot !!

[lboldrino]

thnax but i need to rebulid a new table "table3" filtert by a list of selected values from table 1 and efforts from table 2