powerbidax

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


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?


Solution

  • 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] ) ) )

    enter image description here

    then you can create a matrix

    enter image description here