trying to use related for filter but doesnt work

[avininpowerbi]

Hi ,

I think I got a simple problem to solve but it's eluding me ...

Table 1 ( Fact table) = (All GL codes) ,($), (index id) ( others ) etc etc

Table 2 ( sales) = (Sales GL codes), (SalesID)

Relationship : many:many  ( All GL codes) and (Sales GL codes)

I am trying to write a simple DAX that will filter Table 1 with Sales GL codes from Table 2 and producing a sum value

I was trying to write a DAX but so far, whatever I do, athough  I am getting sum amount at individual Sales GL levels but the sum is total sum of Table 1 . I used RELATED , ALLFILTER - nothing works ...  😞

Can someone help ? Thanks so much

[AllisonKennedy]

Your problem might be the many to many relationship. Do you have a dimension table for GL code - each GL code listed exactly once? That might be a start in helping to solve this. 

@avininpowerbi  Other option is to use INTERSECT with the right combination of tables and columns to compare. Something like this:  INTERSECT(SUMMARIZECOLUMNS(Table1[GLCode],Table1[$]), ADDCOLUMNS(SUMMARIZECOLUMNS(Table2[GLCode]), "$", SUMX(RELATEDTABLE(Table1), Table1[$]))

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[avininpowerbi]

This is an acounting ledger - so , i has GL codes repeated many times in Table 1 for each and every transaction. Howver, For Table 2, I am just using sales codes as once and I am trying to pass this to FILTER the sumx in Table 2 .

its a many to many - no other options it has given me .

My goal is to write a simple DAX measure that i can use to find sales for these codes only.

I tried the INTERSECT formula you suggested - coud not make it work . Thank you for your response

[AllisonKennedy]

It won't let you use 1 to many because the GL code is repeated in the sales table too.

I'm still a little unclear on if your data model is set up properly or exactly what you are trying to achieve, but if you want to get the overall sum for each Sales GL code, you could create a new table using: 

Table = ADDCOLUMNS(DISTINCT(Sales[GL]),"$", sumx(Table1,Table1[$]))

Problem with this is it won't be able to be filtered by anything else in your data model, so ideally you want to try and get the model setup so you have that dimension table for GL codes where they are each listed only once, then use that for your measures and tables. 

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[avininpowerbi]

Let me try to remodel 

I have a series of codes ( likes Sales ) that are standard codes used for COGS and Sales calcualtions and I am trying to pass them to the main fact table to get their $ values independently. Eventuaally, my goal is link them up in a calendat and be able to slice and dice them

so technically, they are not dim tables - they are just tables with codes and a an ID number

Thanks for your help

[v-yiruan-msft · Accepted solution]

Hi @avininpowerbi ,

Please try to create a measure as below:

Measure = CALCULATE(SUM('AllGL'[Gsale]),FILTER('AllGL','AllGL'[GL Codes] in VALUES('Sales'[Sales GL codes])))

If the above formula is not applicable for your scenario, please correct me and provide more details(some sample data and your expected result). Thank you.

Best Regards

Rena