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], "," )
ThxAlot's avatar
ThxAlot
Super User
3 years ago

CONFIG.pbix

 

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

  • 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.