Forum Discussion
Find & count certain values for each column
Hi all,
My first post here, did some searching but could not find anything that resembles what I'd like to have.
I am working on data that has some shortcomings: many cells are "Unknown", "?", "??" or NULL, 0, Empty. Now, I'd like to count how often such values occur per column. I have created something that kinda does what I want. I have copied the existing data source and then used R to do this:
# 'dataset' holds the input data for this script
output2 <- dataset
library(tidyverse)
unknown_values = c("Unknown", "UNKNOWN", "???", "??", "?")
isUnknown <- function(x){
x %in% unknown_values
}
empty_values = c("0", 0, " ", "", NULL, "NULL")
isEmpty <- function(x){
x %in% empty_values
}
output2 = output2 %>%
filter(Status == "Production") %>%
select(-one_of(c("OSStd", "OSSPStd"))) %>% # replace to your needs
gather() %>%
group_by(key) %>%
summarise_all(
funs(
'Unknown' = sum(isUnknown(.)),
'Empty' = sum(isEmpty(.))
)
) %>%
as.data.frame
This solution works but I would also like to filter using the dataframe that my R code spits out which unfortunately does not work out of the box. Makes sense however, because I have two dataframes that are not connected.
An example: a column in the original dataframe (1) is AssetType and another column in datasource (marked in blue).
AssetType
Server
Desktop
0
Empty
Null
Workstation
...
My R code creates a row in a secondary dataframe (2, marked in yellow) as follows:
key -- Unknown -- Empty
AssetType -- 201 -- 16
Now what I would like to happen, is that the table marked in blue only counts the rows where assetType is empty or unknown, which comes down to approx. 8.5% of 2639 = 222 in total. I could do this by making two measures (one for unknown, another one for empty) for each column that I have in dataframe 1, although I am not sure how I would have to select columns in a similar way as shown in the yellow part of the image above.
Is there a better way to do this or a way to link the two dataframes so that the filters will be applied preferably in a bidirectional way?
Kind regards,
Jacco
7 Replies
I find it a bit difficult to understand what are you trying to achieve, can you post sample few rows from the both tables (anonymised)
Dataframe 1Dataframe 2
Df1 contains rows with info about systems. Df2 contains rows that in essence are counts of the columns from Df1.
Regards
ok, so you want to count rows in Dataframe1, that meet certain criteria
in DAX this can be achieved like this
AssetType_Empty= CALCULATE(COUNTROWS(DF1),DF1[AssetType] = BLANK()) AssetType_Unknown= CALCULATE(COUNTROWS(DF1),DF1[AssetType] = "Unknown")
assuming Dataframe2 is supposed to only serve as a source for the dropdown in the yellow visual, the following syntax would work
Empty = VAR ColumnName = SELECTEDVALUE(DF2[key]) RETURN SWITCH(ColumnName, "AssetType",[AssetType_Empty], "Other column",[Other column_Empty], BLANK() )then very similiar measure for Unknown
Is this what you were looking for, or do you look for a way to modify values in DF2?
