Forum Discussion

jk91's avatar
jk91
Frequent Visitor
8 years ago

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", "?"...
Need Help
Stachu's avatar
Stachu
Community Champion
8 years ago

you could create just 2 measures, and include the column specific ones as variables

Empty = 
VAR ColumnName = SELECTEDVALUE(DF2[key])
VAR AssetType_Empty = CALCULATE(COUNTROWS(DF1),DF1[AssetType] = BLANK())
VAR Other_column_Empty = CALCULATE(COUNTROWS(DF1),DF1[Other_column] = BLANK())
RETURN
SWITCH(ColumnName,
    "AssetType",AssetType_Empty,
    "Other column",Other_column_Empty,
    BLANK()
)

if you could unpivot the columns so they would all be in 2 columns like ColumnName & ColumnValue then it's just 2 very simple measures. This approach requires some drastic changes to your data model

jk91's avatar
jk91
Frequent Visitor
8 years ago

Thanks again, I am not sure whether I have made myself as clear as I would like so I'll try to rephrase. What I need is a score for data quality for both the whole dataframe(df1) and every single column in there. My R code simply counts the occurence of empty or unknown per column and saves those two counts in a second dataframe. However, the two df's have different structures and therefore they do not support cross-filtering.

 

What I'd like to have is that I can select a column (e.g. AssetType) using a slicer and that when I apply a filter (e.g. AVClient = "McAfee") the metrics update and only shows the unknown and empty counts for each row where AVClient equals McAfee.

 

However, if I deselect the AssetType column, it should show a score for the whole dataframe where AVClient = McAfee. I guess this requires looping over every cell in the df or calculating two scores for each column beforehand.

 

  • Stachu's avatar
    Stachu
    Community Champion
    8 years ago

    what you're describing in regards to filters is a default behaviour, and assuming I understand you correctly this is how measure works now
    so e.g.
    you filter AssetType out of DF2 column Key to show Empty in AssetTpe column
    whatever kind of filter you apply on DF1 (e.g. slicer DF1[AVClient] = McAfee) will affect the result of the Measure I posted