Power BI is built around using the columns, so most of the functions are optimised to process columns, but every now and again you cannot use columns and you must do something else. A classic example of these are the X functions, many have these down as one of if not THE most complex thing in DAX to master, I know they were not an immediate for me or for anyone I have worked with. The main issue is that DAX is all about context, and the most common context in DAX as a columnar language is filter context – after all how to we know what parts of the column to include / exclude to build a total.
The basic DAX way of doing the calculation was therefore to build and aggregation and then evaluate that against another aggregation, however because “X” functions work row by row we can evaluate each row.
Sum = DIVIDE(SUM(‘Library'[Loans]), SUM(‘Library'[Number of copies]))
AverageX = AVERAGEX(‘Library’, DIVIDE(‘Library'[Loans],’Library'[Number of copies],0))
The gotcha with X functions is because they go row by row, if you have too many rows they will end up taking a very long time to complete.
Data Sources
Newcastle Libraries most borrowed titles : adult fiction
https://data.gov.uk/dataset/26294d28-89da-4f0d-83a7-359ddb522cdb/newcastle-libraries-most-borrowed-titles-adult-fiction
Newcastle Libraries most borrowed titles : junior fiction
https://data.gov.uk/dataset/476150f7-2950-43d2-b2cc-11fcf7e2756c/newcastle-libraries-most-borrowed-titles-junior-fiction
#GeordieIntelligence #PowerBI #DAX #Newcastle