Forum Discussion
How to multiply two different Tables Dax Query
Hi I have this 2 tables that I want to multiply with 3 filters.
Year :
Market Year
Carrier:
First Table 1
| RVUYear | CPT | MOD | CPTmod | descrip | workRVU | NonFacTot | FacTot | YearCPTmod |
| 2022 | 27299 | NA | 27299 | Pelvis/hip joint surgery | 0.00 | 0.00 | 0.00 | 2022 - 27299 |
| 2022 | 25999 | NA | 25999 | Forearm or wrist surgery | 0.00 | 0.00 | 0.00 | 2022 - 25999 |
| 2022 | 27599 | NA | 27599 | Leg surgery procedure | 0.00 | 0.00 | 0.00 | 2022 - 27599 |
| 2022 | 26989 | NA | 26989 | Hand/finger surgery | 0.00 | 0.00 | 0.00 | 2022 - 26989 |
| 2022 | 27899 | NA | 27899 | Leg/ankle surgery procedure | 0.00 | 0.00 | 0.00 | 2022 - 27899 |
| 2022 | 0520F | NA | 0520F | Rad dos limts b/4 3d rad | 0.00 | 0.00 | 0.00 | 2022 - 0520F |
| 2022 | 0520T | NA | 0520T | Rmvl&rplcmt pg wcs new eltrd | 0.00 | 0.00 | 0.00 | 2022 - 0520T |
| 2022 | 0521F | NA | 0521F | Plan of care 4 pain docd | 0.00 | 0.00 | 0.00 | 2022 - 0521F |
| 2022 | 0521T | NA | 0521T | Interrog dev eval wcs ip | 0.00 | 0.00 | 0.00 | 2022 - 0521T |
| 2022 | 21088 | NA | 21088 | Prepare face/oral prosthesis | 0.00 | 0.00 | 0.00 | 2022 - 21088 |
| 2022 | 1034F | NA | 1034F | Current tobacco smoker | 0.00 | 0.00 | 0.00 | 2022 - 1034F |
| 2022 | 21089 | NA | 21089 | Prepare face/oral prosthesis | 0.00 | 0.00 | 0.00 | 2022 - 21089 |
| 2022 | 1035F | NA | 1035F | Smokeless tobacco user | 0.00 | 0.00 | 0.00 | 2022 - 1035F |
| 2022 | 0522T | NA | 0522T | Prgrmg dev eval wcs ip | 0.00 | 0.00 | 0.00 | 2022 - 0522T |
| 2022 | 1036F | NA | 1036F | Tobacco non-user | 0.00 | 0.00 | 0.00 | 2022 - 1036F |
| 2022 | 1038F | NA | 1038F | Persistent asthma | 0.00 | 0.00 | 0.00 | 2022 - 1038F |
| 2022 | 1039F | NA | 1039F | Intermittent asthma | 0.00 | 0.00 | 0.00 | 2022 - 1039F |
| 2022 | 0523T | NA | 0523T | Ntrapx c ffr w/3d funcjl map | 0.00 | 0.00 | 0.00 | 2022 - 0523T |
| 2022 | 1040F | NA | 1040F | Dsm-5 info mdd docd | 0.00 | 0.00 | 0.00 | 2022 - 1040F |
| 2022 | 0524T | NA | 0524T | Ev cath dir chem abltj w/img | 0.00 | 0.00 | 0.00 | 2022 - 0524T |
Table 2
| ServiceCenterRegion | CarrierName | PlanName | CPT | MOD | CPTmod | nCh | sumUnits | sumVolRVU | sumValExp | sumAmtCh | sumAmtApprv | DateTimeUpdate | Proposed RVU Volume | YearCPTmod |
| Portland Metro | Cigna | 99024 | 99024 | 1 | 2.00 | 0.00 | 0.00 | 0.00 | 0.00 | 07/07/2023 17:27 | 2 | 2020 - 99024 | ||
| Portland Metro | Providence | 2028F | 2028F | 1 | 2.00 | 0.00 | 0.00 | 0.00 | 0.00 | 07/07/2023 17:27 | 2 | 2020 - 2028F | ||
| Portland Metro | Regence (BCBS) | 4000F | 4000F | 1 | 2.00 | 0.00 | 0.00 | 0.00 | 0.00 | 07/07/2023 17:27 | 2 | 2020 - 4000F | ||
| Portland Metro | UHC | 99024 | 99024 | 1 | 2.00 | 0.00 | 0.00 | 0.00 | 0.00 | 07/07/2023 17:27 | 2 | 2020 - 99024 | ||
| Southern Willamette | Providence | 1101F | 1101F | 1 | 2.00 | 0.00 | 0.00 | 0.00 | 0.00 | 07/07/2023 17:27 | 2 | 2020 - 1101F | ||
| Southern Willamette | Regence (BCBS) | 1101F | 1101F | 1 | 2.00 | 0.00 | 0.00 | 0.00 | 0.00 | 07/07/2023 17:27 | 2 | 2020 - 1101F | ||
| Southern Willamette | UHC | 99024 | 99024 | 1 | 2.00 | 0.00 | 0.00 | 0.00 | 0.00 | 07/07/2023 17:27 | 2 | 2020 - 99024 | ||
| Central OR | Cigna | J0588 | J0588 | 1 | 2.00 | 0.00 | 0.00 | 0.00 | 0.00 | 07/07/2023 17:27 | 2 | 2021 - J0588 | ||
| Central OR | Regence (BCBS) | A4450 | A4450 | 1 | 2.00 | 0.00 | 0.00 | 0.00 | 0.00 | 07/07/2023 17:27 | 2 | 2021 - A4450 | ||
| Mid-Willamette | Regence (BCBS) | 99024 | 99024 | 1 | 2.00 | 0.00 | 0.00 | 0.00 | 0.00 | 07/07/2023 17:27 | 2 | 2021 - 99024 | ||
| Northeast OR | Providence | 99024 | 99024 | 1 | 2.00 | 0.00 | 0.00 | 0.00 | 0.00 | 07/07/2023 17:27 | 2 | 2021 - 99024 | ||
| Northeast OR | UHC | 99499 | 99499 | 1 | 2.00 | 0.00 | 0.00 | 0.00 | 0.00 | 07/07/2023 17:27 | 2 | 2021 - 99499 | ||
| Portland Metro | Aetna | 2028F | 2028F | 1 | 2.00 | 0.00 | 0.00 | 0.00 | 0.00 | 07/07/2023 17:27 | 2 | 2021 - 2028F | ||
| Portland Metro | MODA | 99024 | 99024 | 1 | 2.00 | 0.00 | 0.00 | 0.00 | 0.00 | 07/07/2023 17:27 | 2 | 2021 - 99024 | ||
| Portland Metro | Regence (BCBS) | 2028F | 2028F | 1 | 2.00 | 0.00 | 0.00 | 0.00 | 0.00 | 07/07/2023 17:27 | 2 | 2021 - 2028F | ||
| Portland Metro | Regence (BCBS) | 99024 | 99024 | 1 | 2.00 | 0.00 | 0.00 | 0.00 | 0.00 | 07/07/2023 17:27 | 2 | 2021 - 99024 | ||
| Portland Metro | UHC | 99024 | 99024 | 1 | 2.00 | 0.00 | 0.00 | 0.00 | 0.00 | 07/07/2023 17:27 | 2 | 2021 - 99024 | ||
| Southern Willamette | Aetna | 1100F | 1100F | 1 | 2.00 | 0.00 | 0.00 | 0.00 | 0.00 | 07/07/2023 17:27 | 2 | 2021 - 1100F | ||
| Southern Willamette | Aetna | 3288F | 3288F | 1 | 2.00 | 0.00 | 0.00 | 0.00 | 0.00 | 07/07/2023 17:27 | 2 | 2021 - 3288F | ||
| Southern Willamette | Cigna | 99024 | 99024 | 1 | 2.00 | 0.00 | 0.00 | 0.00 | 0.00 | 07/07/2023 17:27 | 2 | 2021 - 99024 | ||
| Southern Willamette | First Choice | 1100F | 1100F | 1 | 2.00 | 0.00 | 0.00 | 0.00 | 0.00 | 07/07/2023 17:27 | 2 | 2021 - 1100F | ||
| Southern Willamette | Health Net | G8427 | G8427 | 1 | 2.00 | 0.00 | 0.00 | 0.00 | 0.00 | 07/07/2023 17:27 | 2 | 2021 - G8427 | ||
| Southern Willamette | MODA | 1100F | 1100F | 1 | 2.00 | 0.00 | 0.00 | 0.00 | 0.00 | 07/07/2023 17:27 | 2 | 2021 - 1100F | ||
| Southern Willamette | Providence | 3072F | 3072F | 1 | 2.00 | 0.00 | 0.00 | 0.00 | 0.00 | 07/07/2023 17:27 | 2 | 2021 - 3072F | ||
| Southern Willamette | Regence (BCBS) | 1100F | 1100F | 1 | 2.00 | 0.00 | 0.00 | 0.00 | 0.00 | 07/07/2023 17:27 | 2 | 2021 - 1100F | ||
| Southern Willamette | Regence (BCBS) | 3072F | 3072F | 1 | 2.00 | 0.00 | 0.00 | 0.00 | 0.00 | 07/07/2023 17:27 | 2 | 2021 - 3072F | ||
| Southern Willamette | Regence (BCBS) | G8482 | G8482 | 1 | 2.00 | 0.00 | 0.00 | 0.00 | 0.00 | 07/07/2023 17:27 | 2 | 2021 - G8482 | ||
| Southern Willamette | UHC | 1101F | 1101F | 1 | 2.00 | 0.00 | 0.00 | 0.00 | 0.00 | 07/07/2023 17:27 | 2 | 2021 - 1101F | ||
| Southern Willamette | UHC | 1220F | 1220F | 1 | 2.00 | 0.00 | 0.00 | 0.00 | 0.00 | 07/07/2023 17:27 | 2 | 2021 - 1220F | ||
| Southern Willamette | UHC | G8482 | G8482 | 1 | 2.00 | 0.00 | 0.00 | 0.00 | 0.00 | 07/07/2023 17:27 | 2 | 2021 - G8482 |
not all the data where there. But that is the data structure.
Hi JuradoKevin14 ,
According to your description, here are my steps you can follow as a solution.
(1) This is my test data.
(2) We can create a calculated column.
PostYear = LEFT('Table 2'[YearCPTmod],4)(3) We can create a measure.
Measure = var _a=SELECTEDVALUE('Table 1'[RVUYear]) var _b=SELECTEDVALUE('Table 2'[CarrierName]) var _c=SELECTEDVALUE('Table 2'[PostYear]) var _d=CALCULATE(SUM('Table 2'[ sumUnits ]),FILTER(ALLSELECTED('Table 2'),'Table 2'[CPT]=MAX('Table 1'[CPT]))) var _e=CALCULATE(MIN('Table 1'[NonFacTot]),FILTER(ALLSELECTED('Table 1'),'Table 1'[CPT]=MAX('Table 1'[CPT]))) RETURN _d*_e(4) Then the result is as follows.
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
- lbendlin
Super User
Multiply as in cross join? Or outer join? Inner join?
What's the expected result based on your sample data?
Hi JuradoKevin14 ,
According to your description, here are my steps you can follow as a solution.
(1) This is my test data.
(2) We can create a calculated column.
PostYear = LEFT('Table 2'[YearCPTmod],4)(3) We can create a measure.
Measure = var _a=SELECTEDVALUE('Table 1'[RVUYear]) var _b=SELECTEDVALUE('Table 2'[CarrierName]) var _c=SELECTEDVALUE('Table 2'[PostYear]) var _d=CALCULATE(SUM('Table 2'[ sumUnits ]),FILTER(ALLSELECTED('Table 2'),'Table 2'[CPT]=MAX('Table 1'[CPT]))) var _e=CALCULATE(MIN('Table 1'[NonFacTot]),FILTER(ALLSELECTED('Table 1'),'Table 1'[CPT]=MAX('Table 1'[CPT]))) RETURN _d*_e(4) Then the result is as follows.
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
