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.

v-alq-msft's avatar
v-alq-msft
Icon for Community Support rankCommunity Support
5 years ago

Hi, Anonymous 

 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

SalesBookings:

 

SalesTable:

 

There is a relationship betweent two tables.

 

You may create a measure as below.

Result = 
SUMX(
    SUMMARIZE(
        DISTINCT(SalesTable[SalesId]),
        SalesTable[SalesId],
        "Result",
        SUM(SalesTable[SaleTotal])
    ),
    [Result]
)

 

Result:

 

Best Regards

Allan

 

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

Anonymous's avatar
Anonymous
Not applicable
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.