DAX - select column based on row value in other column

[vistrup]

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 ID	Product Name	Category
12	Bike 12 inc	Bike
35	Nut	Nuts and bolts
40		Bike
11	Bolt

<tbody>
</tbody>

table 2:

Column name

Product ID

Product Name

Category

<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 name	Number of blanks
Product ID	0
Product Name	1
Category	1

<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]

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

 

[vistrup]

Hi Gazpage,

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

 

[gazpage]

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

 

[gazpage]

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]

Hi gazpage,

Sorry for not replying before.

Thank you. I will try it right away

 

[vistrup]

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]

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.