Tech Tuesday topic for today is to provide or share some tips on DAX Functions within Power BI
DAX – Data Analysis Expressions
DAX (Data Analysis Expressions) is a formula expression language and can be used in different BI and visualization tools. DAX is also known as function language, where the full code is kept inside a function.
Here i would like to share some tips on some of most commonly needed DAX Functions:
- How to build CALENDAR Table ?
Function : Hit “Modelling” in Power Bi Desktop >> New Table >> DATES = CALENDAR(DATE(2020,1,1), DATE(2020,12,31))
Now, you can this table columns for reporting or any relationships to other tables
2. How to use IF() DAX Function ? Example below
Example: MONTHS = IF(DATES[DAte].[MonthNo]=1, “January”,IF(DATES[Date].[MonthNo]=2, “February”, IF(DATES[Date].[MonthNo]=3,”March”, IF(DATES[Date].[MonthNo]=4, “April”,IF(DATES[Date].[MonthNo]=5,”May”,IF(DATES[Date].[MonthNo]=6,”June”, IF(DATES[Date].[MonthNo]=7,”July”, IF(DATES[Date].[MonthNo]=8,”August”,IF(DATES[Date].[MonthNo]=9,”September”,IF(DATES[Date].[MonthNo]=10,”October”,IF(DATES[Date].[MonthNo]=11,”November”,”December”)))))))))))
You can verify then post that, with new viz and pulling the column created
3. How to use “SWITCH()” function ? Example Below
We can use same IF() logic here with SWITCH()
MTH = SWITCH(DATES[Date].[MonthNo], 1, “January”, 2, “February”, 3, “March”, 4, “April” , 5, “May”, 6, “June”, 7, “July”, 8, “August” , 9, “September”, 10, “October”, 11, “November”, 12, “December” , “Unknown month number” )
Now, you can test it by using “Table” viz and drag the column into the grid
4. Current Date/Time to display in dashboard
DAX Logic : CURRENT_DATE = NOW()
Now, you can test it by adding “Table” viz and drag the column to verify
If you would like to display as “Date/Time” format – update them in data type and format under “Column tools”
Enjoy Reading!!