Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Many to Many w/Dax

This was a totally different post that I added last week. I have made some progress on my own and sort of made a mess of the post so I'm editing it to ask a more specific question. 

 

I have a data model as shown below. In my fact table I have Territory field. In my dimension table I have the same. However, it's a M2M since there are multiple countries in a single Territory. My question is, since I can't create a typical one2many, is it possible to sum my fans total by territory using DAX then using the territory field from my dim table as a slicer, as I have other tables joined to it for my dashboard? 

 

Thanks in advance for any tips! 

 

 

 

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    8 years ago

    I'm not sure I am following you but if your question is how to calculate a measure that you want explicit filters on but still maintain the context filters as well, then you need to mess around with the ALLEXCEPT function a lot of times or the CALCULATETABLE function, things like that. It gets a little tricky and hairy to be sure. If you can post sample/example data along with expected output for a given scenario that is the quickest way to get things solved.

     

    Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    8 years ago

    I'm not sure I am following you but if your question is how to calculate a measure that you want explicit filters on but still maintain the context filters as well, then you need to mess around with the ALLEXCEPT function a lot of times or the CALCULATETABLE function, things like that. It gets a little tricky and hairy to be sure. If you can post sample/example data along with expected output for a given scenario that is the quickest way to get things solved.

     

    Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Greg_Deckler

       

      Figured it out! Your suggesting I dig into other functions led me to the following solution. If you can't create a physical relationship based on unique values, go virtual. This works perfectly for my needs. 

       

      Hopefully this will help someone else! :)

       

      Happy DAXing! 

       

      Fans = 
CALCULATE (
    SUM ( SocialMedia[Fans] ),
    FILTER ( SocialMedia, SocialMedia[Date] = ENDOFMONTH ( Dates[Date] ) ),
    FILTER (
        ALL ( SocialMedia[Territory] ),
        CONTAINS (
            VALUES ( TerritoryCountryMapping[Territory] ),
            TerritoryCountryMapping[Territory], SocialMedia[Territory]
        )
    )
)

      s