topic Re: Splitting values where keys matching in DAX Commands and Tips

Splitting values where keys matching

Sergei8000 — Tue, 12 Dec 2023 18:18:21 GMT

I have two tables. In table 1 there is about 800 repeating key values. In table 100 unique key values and 20 columns with data values.

I want output where each matching value splitted by number of matches.

Table 1

Table 2
unique_id	Col1	Col2	Col3
A	400	100	20
B	200		800
C	600	800

output
id	Col1	Col2	Col3
A	100	25	5
B	200		800
A	100	25	5
C	300	400
A	100	25	5
A	100	25	5
C	300	400

How to achieve this?

Re: Splitting values where keys matching

Dangar332 — Tue, 12 Dec 2023 18:39:29 GMT

hi, Sergei8000

try below

Column 1= var a = CALCULATE(SUM('Table 2'[Col1])) var b = CALCULATE(COUNT('Table 1'[id])) return DIVIDE(a,b)

repeat for all column

Re: Splitting values where keys matching

Anonymous — Thu, 14 Dec 2023 08:03:27 GMT

Hi Sergei8000 ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create calculated columns.

Col 1 = var a = CALCULATE(SUM('Table 2'[Col1]),FILTER('Table 2','Table 2'[unique_id]=EARLIER('Table 1'[id]))) var b = COUNTROWS(FILTER('Table 1','Table 1'[id]=EARLIER('Table 1'[id]))) return DIVIDE(a,b)

Col 2 = var a = CALCULATE(SUM('Table 2'[Col2]),FILTER('Table 2','Table 2'[unique_id]=EARLIER('Table 1'[id]))) var b = COUNTROWS(FILTER('Table 1','Table 1'[id]=EARLIER('Table 1'[id]))) return DIVIDE(a,b)

Col 3 = var a = CALCULATE(SUM('Table 2'[Col3]),FILTER('Table 2','Table 2'[unique_id]=EARLIER('Table 1'[id]))) var b = COUNTROWS(FILTER('Table 1','Table 1'[id]=EARLIER('Table 1'[id]))) return DIVIDE(a,b)

(3) Then the result is as follows.

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.