how to write a Dax for distinct count from above table to result table

[ABHIPRAYAM]

[ABHIPRAYAM]

Hi @sanalytics  can you plz share the screen shot of the dax which you have done  so it helps me better to understand.

[sanalytics]

Hey @ABHIPRAYAM 

I was solving this problem fromy my office laptop.So could not attach the solution to external link.

Here you go

https://www.transfernow.net/en/dltransfer?utm_source=20230725GwFtEX33

let me know if you need anything else. Would appreciate your like.

Regards

sanalytics

If it is your solution then please like and accept it as solution

[ABHIPRAYAM]

@sanalytics  understood the approch but cant we calculate  without ID tables because if you have about 150 unique names and if we want to find Distinct count how can we do that

[sanalytics · Accepted solution]

Hello @ABHIPRAYAM 

Please find the below DAX code for your ourput. Ideally, This should do this in Power Query or ETL level. But, here it is the dax code.

Output =

VAR _1 =

SUMMARIZE (

    'Table',

    [Name] )

VAR _2 =

ADDCOLUMNS(

_1,"Count",

1 + (Len ( [Name] ) ) - ( LEN( SUBSTITUTE( [Name],",","") ) )

)

VAR _3 =

CROSSJOIN(

_2,Table2 )

VAR _4 =

FILTER(

_3, [ID] <= [Count]

)

VAR _5 =

ADDCOLUMNS(

_4,"name2",

PATHITEM (

SUBSTITUTE ( [Name], ",", "|"),

      [ID]

      ) )

VAR _6 =

SELECTCOLUMNS(

    _5,"ExtractName",[name2]

)

VAR _Result =

DISTINCT(

    _6

)

RETURN

_Result

Please find the below screenshot as well

Hope this will help you.

Regards

sanalytics

If it is your solution then please like and accept it as solution