Forum Discussion

pedroccamaraDBI's avatar
pedroccamaraDBI
Icon for Post Partisan rankPost Partisan
4 years ago
Solved

Calculate through a related table

Hi guys,
I hope everyone's ok.
I'm building a matrix visual that shows me by for each type of policy, the total by 4 levels, Company, cost center, product and partner.
All these columns are in the same table, policies, except the partner which is in partners table, connected actively with policies.
I want to calculate the sum of values for each level and i came up with these measures:

CALCULATE(
[Total], VALUES( Partners),
TREATAS( VALUES( Partners[ContractID]),'Policies'[ContractID] ))
which works but i believe it's taking to long to calculate and sometimes gives me this error, and then it goes away.

Then i came up with another measure, which also is taking to long to calculate:

CALCULATE([Total],
RELATEDTABLE( Partners))

Can you help me on what is the best measure for this one?
Thank you all very much










Need Help DAX
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi pedroccamaraDBI 

    you can try

    Partners Total =
CALCULATE (
    [Total],
    CROSSFILTER ( Partners[ContractID], 'Policies'[ContractID], BOTH )
)

7 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    pedroccamaraDBI , if policies table is connected with a partner then why the need of these filters

    you can check for non blank

    CALCULATE([Total],
    filter(Partners, not(isblank(Partners[ContractID] )))

     

     

    If this does not help
    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

     

    • pedroccamaraDBI's avatar
      pedroccamaraDBI
      Icon for Post Partisan rankPost Partisan
      4 years ago

      That is exactly my point amitchandak 
      I wouldn't need any filter. 
      This table is connected to another table (which doesn't matter because i don't have any measure or column from it) but it's also connected with Dates table with an inactive connection. This also shouldn't matter...

      Can you come up with any other measure solution?

      • amitchandak's avatar
        amitchandak
        Icon for Super User rankSuper User
        4 years ago

        pedroccamaraDBI , Assume you are using table Fact and the fall in filter direction of Dim1 , Dim2 via dim 3, then all these dim should filter fact.

         

        If there is inactive join and you can not active that using userelationship

        you can push that to filter

         

        //Date1 is independent Date table or inactive join 
        new measure =
        var _max = maxx(allselected(Date1),Date1[Date])
        var _min = MINX(allselected(Date1),Date1[Date])
        return
        calculate( sum(Table[Value]), filter('Table', 'Table'[Date] >=_min && 'Table'[Date] <=_max))

         

         

        If this does not help
        Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi pedroccamaraDBI 

    you can try

    Partners Total =
CALCULATE (
    [Total],
    CROSSFILTER ( Partners[ContractID], 'Policies'[ContractID], BOTH )
)