Count Loop for mapping relationship

[wsspglobal]

Hi,

I'm trying to do the below count exercise:

I have a mapping of ID-A to ID-B, and I want to define a universal mapping relationship either "One to Many", "Many to One" or "Many to Many". The purpose of the universal mapping is to make sure if I had to use one ID's revenue to calculate that of the other, I know how to split or combine the revenues. For example, if A11 has a revenue of $3,000 then B22, B23,B24 each gets $1,000. My idea is to count how many of each of the count of ID-A and ID-B is bigger than 1 and if that count is bigger than 0, then there is a "Many relationship". I wonder if there is a more efficient way to write it in Power BI without creating too many columns. Here's the result I did in Excel. This is what I wrote in the last column "= IF( E2>0, "Many B", "One B" ) & " to " & IF(F2>0, "many A ", "one A " )"

I'd like to create a column in table view not a measure. Thank you!

A	B	C	D	E	F	G
ID-A	ID-B	CountIDA	CountIDB	Count of CountIDA > 1	Count of CountIDB > 1	Mapping Relationship
A11	B22	3	2	3	2	Many B to many A
A11	B23	3	1	3	0	Many B to one A
A11	B24	3	1	3	0	Many B to one A
A21	B13	1	1	0	0	One B to one A
A32	B34	1	4	0	4	One B to many A
A33	B34	1	4	0	4	One B to many A
A34	B34	1	4	0	4	One B to many A
A35	B34	1	4	0	4	One B to many A
A45	B22	2	2	2	2	Many B to many A
A45	B41	2	1	2	0	Many B to one A

[v-easonf-msft]

Hi , @wsspglobal 

Could you please tell me whether your problem has been solved?
If it is,  please mark the helpful replies or add your reply as Answered to close this thread.
It will help other community members easily find the solution when they get the similar issue.

Best Regards,
Community Support Team _ Eason

[v-easonf-msft]

Hi , @wsspglobal 

It seems that there is no need to create two auxiliary  columns，“CountIDA”，“Count of CountIDA > 1”.

It is enough to create a  column "CountID A >1 " , because the value of  "CountIDA" is eauql to the value of "Count of CountIDA".

Please try formula as below:

Mapping relationship =
VAR a =
    CALCULATE ( COUNT ( 'Table'[ID-A] ), ALLEXCEPT ( 'Table', 'Table'[ID-A] ) )
VAR b =
    CALCULATE ( COUNT ( 'Table'[ID-B] ), ALLEXCEPT ( 'Table', 'Table'[ID-B] ) )
VAR text1 =
    IF ( a > 1, "Many B", "One B" )
VAR text2 =
    IF ( b > 1, "many A", "one A" )
RETURN
    text1 & " to " & text2

The result will show as below:

Pbix attached 

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[wsspglobal]

Hi @v-easonf-msft 

Thanks for responding. I realized I made a mistake in my Excel table. Please the updated one below:

Column E and F has changed, so Column G changed as a result. Column E should be - for every ID-B, count where CountIDA is >1. Column F should be - for every ID-A, count where CountIDB is >1. This way, there should be a difference between your approach and my approach. Please help again. Thank you and sorry for the mistake. 

A	B	C	D	E	F	G
ID-A	ID-B	CountIDA	CountIDB	Count of CountIDA > 1	Count of CountIDB > 1	Mapping Relationship
A11	B22	3	2	2	1	Many B to many A
A11	B23	3	1	1	1	Many B to many A
A11	B24	3	1	1	1	Many B to many A
A21	B13	1	1	0	0	One B to one A
A32	B34	1	4	0	1	One B to many A
A33	B34	1	4	0	1	One B to many A
A34	B34	1	4	0	1	One B to many A
A35	B34	1	4	0	1	One B to many A
A45	B22	2	2	2	1	Many B to many A
A45	B41	2	1	1	1	Many B to many A

[v-easonf-msft · Accepted solution]

Hi , @wsspglobal 

Writing all to a calculated column will become more cumbersome.

It is recommended to calculate with multiple columns。

Please check the new file attached.

pbix attached

BTY ,you can try to hide the calculated column you don't need  in PowerBI report view.

Best Regards,
Community Support Team _ Eason