Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Help needed with DAX formula in Desktop pls

I am trying to get a DAX formula to work but am stuck so I'm hoping someone can help pls.   I have 3 related tables; a StaffTable (with StaffCode), a SalesBookings table (with SaleId, SaleStaffCode...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Thanks for your help Allan.

     

    Your diagram misses the StaffTable relationship. This has a 1:Many from StaffTable to SalesBookings, and SalesBookings has a M:1 to SalesTable.

     

    I have finally resolved this by changing the SalesBookings to SalesTable relationsip to a two way relationship. I think the 2 relationships out of SalesBookings - both being the Many side - was causing the filtering not to work as expected.

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

Anonymous , that is also the sum of TeamSaleAmt in the bookings table. Need to go to SalesTable ?

 

Try a measure like

 

SalesBySalesperson:=
var _tab = summarize(allselected(SalesBookings),SalesBookings[SaleId])
return
CALCULATE(
SUM(SalesTable[SaleTotal]),
filter(SalesTable, SalesTable[SaleId] in _tab) )

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Thanks for your reply amitchandak.

     

    I tried your formula and am getting the same result as my formula.

    The problem appears to be the SaleStaffCode isn't acting as a filter on the formula when it appears in a visual.

    If I add a table visual and add the SaleStaffCode in one column and the formula in another, I'm still getting the total for all SaleStaffCode's as the result for each code.

    This set of tables are a subset of tables within a bigger data model - I'm trying to incorporate these tables and visuals into an existing dashboard - so not sure if something outside of the visual/DAX is causing this.

    On the face of it, this should work which is why it's been driving me crazy.

    I''m not sure if this helps explain the situation better.