Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Wong calculation with calculate function (deja vu?)

Hi!

I have a problem with the calculate dax function, and i am pretty sure it is about filering within the formula, but since i am not used to DAX, i can't figure out how to do it right. This is for excel, but the problem is applicable in PBI as well. Wht would be the correct formula to achieve what i need.

 

i copy paste the presentation i made

 

 

 

thanjks for your help!!

CALCULATE
DAX

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Thanks for your help Chirag.

    I have the following error: This formula is invalid or incomplete: 'The column 'TeamsComposition[capa]' either doesn't exist or doesn't have a relationship to any table available in the current context.'.

    I think this is because the table TeamsComposition is not directly linked to FactTable (that's why I have to use calculate with the crossfilterfuntion), see the relationship bellow. I am attaching my file as well.

    Here is the link to the file

     

    https://docs.google.com/spreadsheets/d/10mP-bUFeAbMSHAIRuUUUWvw-Kq8fABnx/edit?usp=drive_link&ouid=112902539628287263484&rtpof=true&sd=true

     

    Thanks

  • ChiragGarg2512's avatar
    ChiragGarg2512
    Icon for Solution Sage rankSolution Sage
    3 years ago

    In the DAX expression, the expression is written as product of sums instead it should be sum of products.

     

    It should be Calculate(sumX(FactTable,FactTable[Workload]*related(TeamsComposition[capa])),.....)  instead of Calculate(sum(FactTable[Workload])*sum(TeamsComposition[capa]), .....).

    Only possible if the relationship is one to many.

     

    If the problem continues, pleae share sample data.