Finding unique / distinct values from a row

[Saul_K]

I have a table like this  : I wanted to know the number of storage connected to VM ( Datastore name contains the storage name )

Server Name	Datastores.1.1	Datastores.2.1	Datastores.3.1	Datastores.4.1	Datastores.5.1	Datastores.6.1	Datastores.7.1	Datastores.8.1
VM1	PureStorage05	PureStorage05	PureStorage05	PureStorage02	PureStorage02	PureStorage02	PureStorage05	PureStorage05
VM2	PureStorage05	PureStorage02	PureStorage05	PureStorage05	PureStorage05	PureStorage05	PureStorage02	PureStorage02
VM3	InfiniStorage09	PureStorage06	PureStorage06	PureStorage06	PureStorage01	PureStorage01	PureStorage06	PureStorage06

I am looking for an output like this  :

Server_Name	No_of_Storage_connected	StorageArray
VM1	2	PureStorage05 ,PureStorage02
VM2	2	PureStorage05 ,PureStorage02
VM3	3	InfiniStorage09,PureStorage06,PureStorage01

Could you please help me to find a way to do this  ?

@PowerBI 

[parry2k · Accepted solution]

@Saul_K tweak the measure to filter out N/A:

Storage Count = 
VAR __Table = 
DISTINCT ( 
    UNION ( 
        VALUES ( 'Storage'[Datastores.1.1] ),
        VALUES ( 'Storage'[Datastores.2.1] ),
        VALUES ( 'Storage'[Datastores.3.1] ),
        VALUES ( 'Storage'[Datastores.4.1] ),
        VALUES ( 'Storage'[Datastores.5.1] ),
        VALUES ( 'Storage'[Datastores.6.1] ),
        VALUES ( 'Storage'[Datastores.7.1] ),
        VALUES ( 'Storage'[Datastores.8.1] )
    )
)
RETURN
COUNTROWS ( FILTER ( __Table, [Datastores.1.1] <> "N/A" ) )


Storage Array = 
VAR __Table = 
DISTINCT ( 
    UNION ( 
        VALUES ( 'Storage'[Datastores.1.1] ),
        VALUES ( 'Storage'[Datastores.2.1] ),
        VALUES ( 'Storage'[Datastores.3.1] ),
        VALUES ( 'Storage'[Datastores.4.1] ),
        VALUES ( 'Storage'[Datastores.5.1] ),
        VALUES ( 'Storage'[Datastores.6.1] ),
        VALUES ( 'Storage'[Datastores.7.1] ),
        VALUES ( 'Storage'[Datastores.8.1] )
    )
)
RETURN
CONCATENATEX ( FILTER ( __Table, [Datastores.1.1] <> "N/A" ), [Datastores.1.1], "," )

[parry2k]

@ThxAlot that was my first recommendation in my reply.

[ThxAlot]

CONFIG.pbix

Isn't it enough simple after you unpivot the table?

[Saul_K]

Thanks for the response.  I am very new to Powerbi and not good with the unpivot option, I will take a look. Thank you for the suggetion. 

[parry2k]

@Saul_K Usually you want unpivoted data but with the current structure, you can add the following two measures to get the result;

Storage Count = 
VAR __Table = 
DISTINCT ( 
    UNION ( 
        VALUES ( 'Storage'[Datastores.1.1] ),
        VALUES ( 'Storage'[Datastores.2.1] ),
        VALUES ( 'Storage'[Datastores.3.1] ),
        VALUES ( 'Storage'[Datastores.4.1] ),
        VALUES ( 'Storage'[Datastores.5.1] ),
        VALUES ( 'Storage'[Datastores.6.1] ),
        VALUES ( 'Storage'[Datastores.7.1] ),
        VALUES ( 'Storage'[Datastores.8.1] )
    )
)
RETURN
COUNTROWS ( __Table )


Storage Array = 
VAR __Table = 
DISTINCT ( 
    UNION ( 
        VALUES ( 'Storage'[Datastores.1.1] ),
        VALUES ( 'Storage'[Datastores.2.1] ),
        VALUES ( 'Storage'[Datastores.3.1] ),
        VALUES ( 'Storage'[Datastores.4.1] ),
        VALUES ( 'Storage'[Datastores.5.1] ),
        VALUES ( 'Storage'[Datastores.6.1] ),
        VALUES ( 'Storage'[Datastores.7.1] ),
        VALUES ( 'Storage'[Datastores.8.1] )
    )
)
RETURN
CONCATENATEX ( __Table, [Datastores.1.1], "," )

[Saul_K]

Thanks a lot , that helps . I am very new to power bi and my questions may feel silly 🙂  I have one more ask,how we can ignore 'Null' / 'N/A' values when you run the above query ? I had null values before, but I changed  it to N/A. 

The below is the sample table. 

As there is N/A value in most of the row, it calculate that as a value and add it in to count and storage array output.

Server_Name	No_of_Storage_connected	StorageArray
VM1	2	PureStorage05 ,PureStorage02
VM2	3	PureStorage05 ,PureStorage02,N/A
VM3	4	InfiniStorage09,PureStorage06,PureStorage01,N/A