DAX Time Intelligent – Single Measure For Multiple Dates

Let’s assume a scenario where we have 50 time intelligent measures and 5 dimension Dates (order, invoice, ship, due, calendar…).
If we want the 50 measure to interact with the 5 dimensions we would need to create 250 measures which might rapidly overwhelm the users as they must sift though so many measures.

This script below allows you to drag any dates with the YTD margin measure and the measure will dynamically apply the calculation accordingly and thus keeps our number of measures to 50 instead of 250.

Total Margin YTD:=
var AllDate = COUNTROWS(all('Order Date'))
--you can use any date dimension as long as they have the same number of rows
--you can also create a separate measure for the count all to avoid
--recomputing and retyping the var calculation each time
COUNTROWS('Order Date')<AllDate,TOTALYTD([Internet Total Margin],'Order Date'[Order Date]) ,
COUNTROWS('Due Date')<AllDate,TOTALYTD([Internet Total Margin],'Due Date'[Due Date]),
COUNTROWS('Ship Date')<AllDate,TOTALYTD([Internet Total Margin],'Ship Date'[Ship Date]),
TOTALYTD([Internet Total Margin],'Order Date'[Order Date])

Note that you can also use the function IsCrossFIltered(datekey) instead of CountRows() but you cannot use IsFiltered() or HasOneFiltered() as it returns TRUE only when a column is being filtered directly.
So far I seem to have just a little bit better performance with the countrows() version but I haven’t really tested it on a large database.

The main drawback of it is that we need to create many role-playing dimension instead of keeping only one dimension and taking advantage of the UseRelationship DAX function. But role-playing date dimensions are very small so memory consumption won’t be an issue anyway!

Here I use the same measure with three different dates. (AdventureWorks2016 sample data)


This technique might become useless once the new “calculation group”  feature of SSAS 2019 will be released.


PowerBI – Dynamic Chart Title

Unlike Qlikview, the chart titles in PowerBI can only be static. as you can only pass a static text in the title parameter.


However, there’s a way around it!
The workaround I found is pretty simple you just need to fake a title by creating a measure which contains your title expression and drop this measure into a Card visual .

Then by applying the same transparency and colours of your chart you just need to turn off the chart tile and put the Card visual on top of your chart.

Here is the code for my  title measure:

 MyMeasureTitle = ("Total Cost of the Top " & [TopN Value] & " Depts VS all other Depts") 


So my title will interact with the above slicer dynamically however if no values are ticked off I still want a default value to be returned so here is the code for this (you might not need to implement it)

TopN Value =
IF (
        HASONEVALUE ('TopN Filter'[TopN])
         , VALUES ('TopN Filter'[TopN])
         , 10

So after dropping your measure into a Card visual you’ve got your title ready!


And this how it looks when you place it right above your chart:


Make sure your chart and the card have the same size and colour and by setting the right location x,y it will look like the embedded chart title.