Forum Discussion
Iterate Values from 2 different Tables to get Output Table
Hello;
I have two tables "Table_Req" and "Table_Cap", I need to find the EmployeeId's on "Table_Cap" matching ParameterKey for each "DemandId". Some kind of iteration will be required. Can you please help me to get the desired result? Herein the dummy example for the data set and desired result:
Tables in the Data Model:
| Table_Req | |
| DemandId | ParameterKey |
| 123 | A |
| 123 | B |
| 123 | D |
| 123 | F |
| 234 | C |
| 234 | D |
| 234 | E |
| 345 | E |
| 345 | F |
| 345 | G |
| 456 | B |
| 456 | C |
| 456 | D |
| Table_Cap | |
| EmployeeId | ParameterKey |
| XYZ | A |
| XYZ | B |
| XYZ | C |
| XYZ | D |
| XYZ | E |
| XYZ | F |
| WXY | A |
| WXY | B |
| WXY | C |
| WXY | D |
| VWX | B |
| VWX | C |
| VWX | D |
| VWX | E |
| VWX | F |
| VWX | G |
| UVW | A |
| UVW | B |
| UVW | C |
| UVW | D |
| UVW | F |
Desired Result:
| Output _Table | |
| DemandId | EmployeeMatch |
| 123 | XYZ |
| 123 | UVW |
| 234 | XYZ |
| 234 | VWX |
| 345 | VWX |
| 456 | XYZ |
| 456 | WXY |
| 456 | VWX |
| 456 | UVW |
Thanks in Advance;
WGAP75
Hi Anonymous ，
You need a measure as below:
Measure = IF(CALCULATE(COUNTROWS('Table_Req'),FILTER('Table_Req','Table_Req'[ParameterKey] in DISTINCT(Table_Cap[ParameterKey])))= COUNTROWS('Table_Req'),1,BLANK())Finally you will see:
Pls note that dont create relationships between the 2 table,otherwise the measure will return error.
For the related .pbix file,pls click here.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
3 Replies
v-kelly-msftCommunity Support
Hi Anonymous ，
You need a measure as below:
Measure = IF(CALCULATE(COUNTROWS('Table_Req'),FILTER('Table_Req','Table_Req'[ParameterKey] in DISTINCT(Table_Cap[ParameterKey])))= COUNTROWS('Table_Req'),1,BLANK())Finally you will see:
Pls note that dont create relationships between the 2 table,otherwise the measure will return error.
For the related .pbix file,pls click here.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!Hi v-kelly-msft;
Thanks for your useful help provided with this solution, I've an additional question, How I can do if I need to perform aggregations with the results from the Measure? I tried to COVERT / FORMAT the measure to Number but I don't manage to get a good result.
Again your help is more than Welcome;
WGAP75
camargos88Community Champion
Hi Anonymous ,
I think this link can help you:
https://radacad.com/append-vs-merge-in-power-bi-and-power-query
Ricardo