I have two tables:
Table 1
| PROJECT_NUMBER | PROJECT_NAME |
|---|---|
| 1 | First Project |
| 2 | Second Project |
| 3 | Third Project |
Table 2
| PROJECT_NUMBER | MARKET | VALUE |
|---|---|---|
| 1 | Food | 10 |
| 1 | Retail | 100 |
| 1 | Fashion | 200 |
| 2 | Food | 300 |
| 2 | Retail | 100 |
| 3 | Fashion | 500 |
| 3 | Retail | 10 |
Table 2 has a many to one relationship with Table 1, using the Project Number column.
The goal is that my final table shows me the following:
<code>PROJECT_NUMBER PROJECT_NAME MARKET VALUE ....
1 First Project Fashion 200
2 Second Project Food 300
3 Third Project Fashion 500
</code>
<code>PROJECT_NUMBER PROJECT_NAME MARKET VALUE ....
1 First Project Fashion 200
2 Second Project Food 300
3 Third Project Fashion 500
</code>
PROJECT_NUMBER PROJECT_NAME MARKET VALUE ....
1 First Project Fashion 200
2 Second Project Food 300
3 Third Project Fashion 500
What I am getting now is a list of all the markets and a duplication of the projects for each market associated with them.
<code>PROJECT_NUMBER PROJECT_NAME MARKET VALUE ....
1 First Project Food 10
1 First Project Retail 100
1 First Project Fashion 200
2 Second Project Food 300
2 Second Project Retail 100
3 Third Project Fashion 500
3 Third Project Retail 10
</code>
<code>PROJECT_NUMBER PROJECT_NAME MARKET VALUE ....
1 First Project Food 10
1 First Project Retail 100
1 First Project Fashion 200
2 Second Project Food 300
2 Second Project Retail 100
3 Third Project Fashion 500
3 Third Project Retail 10
</code>
PROJECT_NUMBER PROJECT_NAME MARKET VALUE ....
1 First Project Food 10
1 First Project Retail 100
1 First Project Fashion 200
2 Second Project Food 300
2 Second Project Retail 100
3 Third Project Fashion 500
3 Third Project Retail 10
I tried the following query, trying to replicate this post
<code>MAX_VALUE = MAXX( FILTER('Table 2', 'Table 2'[PROJECT_NUMBER] = 'Table 1'[PROJECT_NUMBER]), 'Table 2'[VALUE])
</code>
<code>MAX_VALUE = MAXX( FILTER('Table 2', 'Table 2'[PROJECT_NUMBER] = 'Table 1'[PROJECT_NUMBER]), 'Table 2'[VALUE])
</code>
MAX_VALUE = MAXX( FILTER('Table 2', 'Table 2'[PROJECT_NUMBER] = 'Table 1'[PROJECT_NUMBER]), 'Table 2'[VALUE])
In your table visual, do the following:
- Set the
MARKETcolumn toFirst
- Set the
VALUEcolumn toMaximum
Final Result:
You can create a CC to get the maximum market value per project :
<code>MaxValuePerProject =
CALCULATE(
MAX('Table 2'[VALUE]),
ALLEXCEPT('Table 2', 'Table 2'[PROJECT_NUMBER])
)
</code>
<code>MaxValuePerProject =
CALCULATE(
MAX('Table 2'[VALUE]),
ALLEXCEPT('Table 2', 'Table 2'[PROJECT_NUMBER])
)
</code>
MaxValuePerProject =
CALCULATE(
MAX('Table 2'[VALUE]),
ALLEXCEPT('Table 2', 'Table 2'[PROJECT_NUMBER])
)
and another one to flag the rows with the maximum value :
<code>IsMaxValue =
IF('Table 2'[VALUE] = 'Table 2'[MaxValuePerProject], TRUE(), FALSE())
</code>
<code>IsMaxValue =
IF('Table 2'[VALUE] = 'Table 2'[MaxValuePerProject], TRUE(), FALSE())
</code>
IsMaxValue =
IF('Table 2'[VALUE] = 'Table 2'[MaxValuePerProject], TRUE(), FALSE())