Forum Discussion

NicoH's avatar
NicoH
Regular Visitor
4 years ago

Need help on DAX function

Hi everyone I'm confused by the DAX function. I want to compare the budget and contract amount based on a table with the budget and contract info with the columns like BudgetID, Year, Budget Amount,...
Need Help
NicoH's avatar
NicoH
Regular Visitor
4 years ago

Hi vojtechsima

Thanks for your tip.

Please find the sample data

budgetIDYeardepartmentbudgetAmount
1102021IT4900
1102022IT4700
2002021HR1000
2002022HR2000

 

budgetIDYearcontractIDcontractAmount
1102021c1100
1102022c2200
1102021c3300
1102022c4400
1102023c130
2002021HR-C1100
2002022HR-C1200
2002022HR-C2500

 

 

THe expected result

YearDepartmentBudgetAmountContractAmount
2021IT4900400
2022IT4700600
v-janeyg-msft's avatar
v-janeyg-msft
Community Support
4 years ago

Hi, NicoH 

 

Are you sure that the columns on the dimension table are all placed in the table?

 

 

  • NicoH's avatar
    NicoH
    Regular Visitor
    4 years ago

    Sorry, here is the table structure.

    Budget(budget id, year, department, budget amount)

    Contract(budget id, year, contract id, contract amount)

     

    • v-janeyg-msft's avatar
      v-janeyg-msft
      Community Support
      4 years ago

      NicoH 

       

      I mean, are you using the columns from the dimension table in visual? 

       

      • NicoH's avatar
        NicoH
        Regular Visitor
        4 years ago

        Yes, I tried and it can calculate the budget amount correctly, but the contract amount did not.

        Now I'm using the composite key for 2 tables: budgetid_year in budget table, budgetid_year_contractid in contract table.