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

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)

jk91's avatar
jk91
Frequent Visitor
8 years ago

Dataframe 1Dataframe 2

Df1 contains rows with info about systems. Df2 contains rows that in essence are counts of the columns from Df1.

 

Regards

  • Stachu's avatar
    Stachu
    Community Champion
    8 years ago

    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?

     

     

    • jk91's avatar
      jk91
      Frequent Visitor
      8 years ago

      Hi Stachu,

       

      Thanks for your help. Using your solution, I'd have to manually create two measures (x_empty & x_unknown) for each column x I guess? And for an overall accuracy / integrity measure i'd have to combine those measures into a single score. That could work although it would be quite a hassle with 35 rows hehe. 

       

      Kind regards

       

       

      • 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