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 NameDatastores.1.1Datastores.2.1Datastores.3.1Datastores.4.1Datastores.5.1Datastores.6.1Datastores.7.1Datastores.8.1
VM1PureStorage05PureStorage05PureStorage05PureStorage02PureStorage02PureStorage02PureStorage05PureStorage05
VM2PureStorage05PureStorage02PureStorage05PureStorage05PureStorage05PureStorage05PureStorage02PureStorage02
VM3InfiniStorage09PureStorage06PureStorage06PureStorage06PureStorage01PureStorage01PureStorage06PureStorage06

 

 

I am looking for an output like this  :

Server_NameNo_of_Storage_connected StorageArray
VM12PureStorage05 ,PureStorage02
VM22PureStorage05 ,PureStorage02
VM33InfiniStorage09,PureStorage06,PureStorage01

 

 

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

PowerBI 

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], "," )

6 Replies

  • 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

       

       

    • Saul_K's avatar
      Saul_K
      Regular Visitor
      3 years ago

      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's avatar
    parry2k
    Super User
    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], "," )