Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
lboldrino
Resolver I
Resolver I

Filter a table with list of selected values from other tables

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:

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

 

 

 

view effort:

employeeeffortbooking_element
em13,2CPSI599-1
em23CPSI599-1
em31CPSI599-12
em40,6CPSI600-1
em42CPSI511
em75,5CPSI500-1
em101CPSI500
em80,13CPSI599-12
em91CPSI600-1
1 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

View solution in original post

8 REPLIES 8
lboldrino
Resolver I
Resolver I

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
Super User
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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>

 

 

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

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

amitchandak
Super User
Super User

@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

 

It works perfectly...thx a lot !!

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

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors