cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Tamir Member
Member

k-mean clustering table or measure

Hello,

 

When using the K-Maen clustering visual, can I get the cluster value of each record in a table?

 

Can the cluster be saved as a measure? 

 

Thank you,

Tamir

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: k-mean clustering table or measure

Hey,

 

as you have to consider the k-means visual as a closed job, this will not be possible.

 

I assume, you are somewhat familiar with R, at least you already have R installed locally, and also managed to install the required R packages to make the visual work :-)

 

Then you might consider to use R from inside Power Query using the 

 

Here is a very simple example, I use an R script as data source (Get Data --> Other --> R script):

 

#loads the iris dataset
data(iris)

# converts the iris dataset do a data.frame corresponds to a Power BI Table
df.iris <- as.data.frame(iris)

# removes the dataset from memory, the data.frame is still avalable
rm(iris)

This will create a table called df.iris, now i'm able to run a script from inside Power Query, of course it's not necessary to use an R script to perform the "Run R script" transformation, this is just to make R example complete, basically each table can be used as input, there can also be more than one R tasks used in one query. 

 

image.png

 

here I will use this very simple R script 

 

iriscluster <- kmeans(dataset[,1:4], 3,nstart = 20)
cluster <- iriscluster$cluster
df.iris <- cbind(dataset,cluster)

now my query has an additional column called cluster.

 

Unfortunately this approach has its drawbacks because a user can not interact (meaning filter) the initial dataset which may result to different cluster, but nevertheless you can use R to create additional information.

 

You also have to be aware that currently R script are currently not support by the "enterprise" mode of the on-premises gateway, just in the "personal" mode. So if you want to / have to share your data with your colleagues can become another challenge.

 

Hopefully this gives you an idea.

 

Regards,

Tom

 

 

 

 

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
1 REPLY 1
Highlighted
Super User
Super User

Re: k-mean clustering table or measure

Hey,

 

as you have to consider the k-means visual as a closed job, this will not be possible.

 

I assume, you are somewhat familiar with R, at least you already have R installed locally, and also managed to install the required R packages to make the visual work :-)

 

Then you might consider to use R from inside Power Query using the 

 

Here is a very simple example, I use an R script as data source (Get Data --> Other --> R script):

 

#loads the iris dataset
data(iris)

# converts the iris dataset do a data.frame corresponds to a Power BI Table
df.iris <- as.data.frame(iris)

# removes the dataset from memory, the data.frame is still avalable
rm(iris)

This will create a table called df.iris, now i'm able to run a script from inside Power Query, of course it's not necessary to use an R script to perform the "Run R script" transformation, this is just to make R example complete, basically each table can be used as input, there can also be more than one R tasks used in one query. 

 

image.png

 

here I will use this very simple R script 

 

iriscluster <- kmeans(dataset[,1:4], 3,nstart = 20)
cluster <- iriscluster$cluster
df.iris <- cbind(dataset,cluster)

now my query has an additional column called cluster.

 

Unfortunately this approach has its drawbacks because a user can not interact (meaning filter) the initial dataset which may result to different cluster, but nevertheless you can use R to create additional information.

 

You also have to be aware that currently R script are currently not support by the "enterprise" mode of the on-premises gateway, just in the "personal" mode. So if you want to / have to share your data with your colleagues can become another challenge.

 

Hopefully this gives you an idea.

 

Regards,

Tom

 

 

 

 

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!