Need to calculate distinct count and percentage in a single row using power bi dax

Question

I have below table in power bi.

id	name	date
1	A	30-April-2023
2	A	30-April-2023
1	A	30-April-2023
4	A	30-April-2023
3	B	30-April-2023
I	B	30-April-2023

I need a pivot table visual like motioned below.

.	A	B
A	3	1/2(50%)
B	1/2(50%)	2

example if A to A total distinct ID count is 3, and A to B is one part is shared among total parts of of b which is 1/2 nothing but 50%

This used to happen based user selection in slicer which is date.

How to this in DAX?

Answer 1

you can try this

1. create a new table

   Table 2= VAR _tbla = DISTINCT ( 'Table'[name] )

   VAR _tblb = SELECTCOLUMNS ( DISTINCT ( 'Table'[name] ), "name1", 'Table'[name] )

   RETURN CROSSJOIN ( _tbla, _tblb )

2. create a Column

   Column= VAR _list = DISTINCT ( SELECTCOLUMNS ( FILTER ( 'Table', 'Table'[name] = 'Table 2'[name] ), "id", 'Table'[id] ) )

   VAR _list2 = DISTINCT ( SELECTCOLUMNS ( FILTER ( 'Table', 'Table'[name] = 'Table 2'[name1] ), "id", 'Table'[id] ) )

   VAR _count = COUNTROWS ( INTERSECT ( _list, _list2 ) )

   RETURN IF ( 'Table 2'[name] = 'Table 2'[name1], CALCULATE ( DISTINCTCOUNT ( 'Table'[id] ), FILTER ( 'Table', 'Table'[name] = 'Table 2'[name] ) ), _count / CALCULATE ( DISTINCTCOUNT ( 'Table'[id] ), FILTER ( 'Table', 'Table'[name] = 'Table 2'[name] ) ) )

then you can create a matrix