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  I tried your suggestion but no luck.

I have 2 tables: Budget and Contract. 

Budget Table

budget id | Year | budget amount

110 | 2021 | 4900

110 | 2022 | 4710.4 

 

Contract Table

BudgetID | contract ID | Year | contract amount

110          | c1               | 2021|  100

110          | c2               | 2021|  200

110          | c3               | 2022|  300

110          | c4               | 2022|  400

 

The result I want likes:

Budget ID | Year | Budget Amount | Contract Amount 

110           | 2021| 4900                   | 300

110           | 2022| 4710.4                | 700

 

Thanks in advance.

voji

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

    Hi, NicoH 

     

    I checked your model, your two tables have many-to-many relationships, what you need is to establish a master-slave table to improve the data model.

    You can use distinct function to create single tables as slave table. According to the information you provide, you need to set a one-to-many relationship between budget id and year.

    Like this:

    Table = DISTINCT('Budget Table'[budget id])
    Table 2 = DISTINCT('Budget Table'[Year])

    Then you need to use slave table budget id and year and master columns in visual.

     

    Did I answer your question? Please mark my reply as solution. Thank you very much.
    If not, please feel free to ask me.

    Best Regards,
    Community Support Team _ Janey

    • NicoH's avatar
      NicoH
      Regular Visitor
      4 years ago

      Hi Janey

      Your solution works for the budget amount, but the contract amount does not work