Querying DMV Data Types in DAX Studio

[CHeard192]

Hi all,

In DAX Studio, I've been querying the DMV using:

select * from $SYSTEM.TMSCHEMA_COLUMNS

There are two columns (ExplicitDataType and InferredDataType) which contain ID's to data types used in Power BI.

However, I'm unable to find somewhere with the dimension table for these data types.

Is anyone able to point me in the right direction?

[Sahir_Maharaj · Accepted solution]

Hello @CHeard192,

The $SYSTEM.TMSCHEMA_COLUMNS DMV in DAX Studio provides information about columns, including data type details, but it doesn't directly map to a dimension table for data types.

However, you can access information about data types using DAX functions:

1. DATATABLE: The DATATABLE function lets you create a table with explicit data types for columns.

2. DATATYPE: The DATATYPE function returns the data type of an expression.

3. ISNUMBER, ISTEXT, ISDATE: These functions return TRUE or FALSE based on whether the expression's data type matches the function's name.

4. VALUES: The VALUES function can be used to retrieve distinct values from a column, which can also give you an idea of the data type of that column.

5. COLUMNS: The COLUMNS function returns a table of columns from a table or table expression along with their data types.

Should you require further assistance, please do not hesitate to reach out to me.

[CHeard192]

Huge help. Thanks Sahir!