Power BI – Finding the data type when using Variables in Measures
I have been working with an Organization where I have had to create some rather complex measures. And within these measures I am using a fair number of variables.
I found that at times it was a challenge to understand what data type was being returned by the variable. Which at times would lead to a different result, because the Variable data type being returned was different from what was expected.
For example, in my measure I wanted to get a date range, if the variable was not a date or date/time data type then my measure would fail.
So below I will show you how to easily find out what the Variable data type is.
Last Year Sales = VAR Date_MaxDate = MAX ( 'Orders'[Order Date] ) VAR Number_TotalDiscount = SUM ( 'Orders'[Discount] ) VAR Text_CustomerName = LASTNONBLANK ( 'Orders'[Customer Name], Number_TotalDiscount ) RETURN Date_MaxDate
- Above is my measure and as it currently stands it will return the value for the variable Date_MaxDate which is created on lines 2 & 3.
- Now I selected the measure name [Last Year Sales] under fields in Power BI Desktop.
I then clicked on Modeling in the ribbon and under Formatting it shows me that the data type is Date Time, as shown below.
- The data type for my Variable called Date_MaxDate is Date Time.
If I now changed the result to be Number_TotalDiscount on line 9, it then shows me that the data type could be any numerical format
And finally, if I change the result to be Text_CustomerName on line 9, it then shows me that the data type is Text
As I have shown in my blog post above, a quick and easy way to see what data type is being returned by the variable.
If you have any comments or questions please leave them in the section below.