# DAX - select column based on row value in other column



## vistrup (Jun 27, 2017)

Hi, 

I have 2 tables. A fact table (table 1) and a table (table 2) that one have one column which contains all the column names from the fact table. 

table 1:

Product IDProduct NameCategory12Bike 12 incBike35NutNuts and bolts40Bike11Bolt

<tbody>

</tbody>

table 2:

Column nameProduct IDProduct NameCategory

<tbody>

</tbody>

What I would like to do is as following. Creating a new column in table 2 that calculated the number of blank rows in table 1 for the column specified in the first column in table 2. 

Table 2 after calc:

Column nameNumber of blanksProduct ID0Product Name1Category1

<tbody>

</tbody>

I can do it in Excel with the following formula: {=COUNTBLANK(INDEX(Table1;;MATCH([@[COLUMN NAME]];Table1[#Headers];0)))}

Any ideas on how to do so?

Thanks in advanced 

Br
Vistrup


----------



## gazpage (Jun 27, 2017)

https://blog.crossjoin.co.uk/2015/06/01/using-selectcolumns-to-alias-columns-in-dax/


----------



## vistrup (Jun 27, 2017)

Hi Gazpage, 

Thanks for the link. However I cannot see how it should help me that I rename the columns in table 1?


----------



## gazpage (Jun 27, 2017)

I thought you could use it to get a column based on it's name, but now I'm not so sure.


----------



## gazpage (Jun 27, 2017)

Ok, I typed in your data in table 1 manually and unpivotted using the following query (I called it 'Unpivot data')


```
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRS0lFyysxOVTA0UsjMS4bylGJ1opWMTYE8v9ISCFmskJiXopCUn1NSDJY1MQCKIys3NATxgPIg8dhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Product ID" = _t, #"Product Name" = _t, Category = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product ID", Int64.Type}, {"Product Name", type text}, {"Category", type text}}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
    #"Transposed Table" = Table.Transpose(#"Demoted Headers"),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Transposed Table", {"Column1"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1", "Column Name"}})
in
    #"Renamed Columns"
```

I then created a table with just the column names using.


```
let
    Source = #"Unpivot data",
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Column Name"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns")
in
    #"Removed Duplicates"
```

Presumably you wouldn't need to do this ad you would already have the table.

I then deleted the relationship that was created automatically and added the following calculated column to this new table.


```
Number of Blanks = 
CALCULATE (
    COUNTROWS ('Unpivot data')+0,
    FILTER (
        'Unpivot data',
        'Unpivot data'[Column Name] = 
            'ColumnNames'[Column Name] &&
            'Unpivot data'[Value] = BLANK()
    )
)
```

Seems to do the trick.


----------



## vistrup (Jul 24, 2017)

Hi gazpage,

Sorry for not replying before. 

Thank you. I will try it right away


----------



## vistrup (Jul 25, 2017)

Hi again,

The only thing I can't get to working is the second line the first code (the source line). My guess is that this ensures that blanks are shown in the unpivotted tabel?


----------



## vistrup (Jul 25, 2017)

Great. Blanks are shown in power bi but not excel  

Furthermore I found out about the source line. it is how it is stated when using power query in power bi.


----------

