Forum Discussion

Saul_K's avatar
Saul_K
Regular Visitor
3 years ago
Solved

Finding unique / distinct values from a row

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 Datast...
Need Help
  • parry2k's avatar
    parry2k
    3 years ago

    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's avatar
parry2k
Super User
3 years ago

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's avatar
    Saul_K
    Regular Visitor
    3 years ago

    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_NameNo_of_Storage_connected StorageArray
    VM12PureStorage05 ,PureStorage02
    VM23PureStorage05 ,PureStorage02,N/A
    VM34InfiniStorage09,PureStorage06,PureStorage01,N/A