Forum Discussion
Measure to concatenate more value using relationship
Hello all,
I have the following tables on my model
| Project | ID_project | Value | ID_project | ID_oppo | ID_oppo | Opportunity | ||
| abc | 1 | 1 | 1 | 2 | 1 | A | ||
| dsc | 2 | 2 | 1 | 3 | 2 | A | ||
| sdf | 3 | 4 | 2 | 4 | 3 | B | ||
| ewe | 4 | 4 | 3 | 4 | 4 | B | ||
| dsd | 5 | 3 | 3 | 2 | 5 | B | ||
| fgt | 9 | 4 | 4 | 1 | 6 | C | ||
| trs | 0 | 3 | 7 | 7 | 7 | C | ||
| 8 | 3 | 8 | C | |||||
| 9 | 8 | 9 | C | |||||
| 0 | 9 | 0 | D |
The relation is:
first 1:* second
second *:1 first
I need to obtain a measure that concatenate all projects belong to a Opportunity and a mesure that do sum of value of project for a Opportunity.
Result table:
Projects and total should be measures
| Oppo | Projects | Total |
| A | abc,sdf,ewe | 9 |
| B | abc,dsc,sdf | 7 |
| C | fgt,trs | 7 |
| D | 0 |
I understand it is very difficult code. I even don't know if it is possible. any kind of idea o improvement is valued
Some help is very appreciated
many many thanks
Projects = CALCULATE( CONCATENATEX('Table 1', 'Table 1'[Project], ", "), TREATAS( VALUES( 'Table 2'[ID_project]),'Table 1'[ID_project])) Total = COALESCE( CALCULATE( SUM('Table 1'[Value]), TREATAS( VALUES('Table 2'[ID_project]),'Table 1'[ID_project])), 0)
3 Replies
johnt75Super User
Projects = CALCULATE( CONCATENATEX('Table 1', 'Table 1'[Project], ", "), TREATAS( VALUES( 'Table 2'[ID_project]),'Table 1'[ID_project])) Total = COALESCE( CALCULATE( SUM('Table 1'[Value]), TREATAS( VALUES('Table 2'[ID_project]),'Table 1'[ID_project])), 0)First of all, very thanks.
First formula works, the problema is total at the end of table that is giant, there is a way to deactivate total only for a mesaure?
The secondo one doesn't work, all value are zero. Do you have any idea about this problem?
- johnt75
You can replace the first measure with
Projects = IF( ISINSCOPE('Table 3'[Opportunity]), CALCULATE( CONCATENATEX('Table 1', 'Table 1'[Project], ", "), TREATAS( VALUES( 'Table 2'[ID_project]),'Table 1'[ID_project])))Not sure what might be wrong with the 2nd, as it works for me