DAX doesn't have the concept of global variables so reusing a piece of code by referencing it somewhere else isn't possible, luckily the DETAILROWS function in DAX allows you to reuse the filters/table specified in a dedicated measure and let's you reuse that code in some other measure.
For example I have a filter such as the following which retuns the combination of some columns based on filters applied and then I do some xyz computation and get the result.
As you can see in the above image the TableFilter variable is something that can't be used in different queries and if you were to create other measures you can't just reference TableFilter variable directly because there is no such thing as global/public variable in DAX
This is where DETAILROWS comes into picture.
First we use the code of TableFilter variable in a Measure's Detail Rows Expression using Tabular Editor, please note the code is a bit different because in the Test Measure KEEPFILTERS was in the CALCULATE but in Detail Row Expression there is only CALCULATETABLE so the appropriate intersection with KEEPFILTERS is necessary.
Now we can use can call this measure as a table inside CALCULATE in a new measure which uses DETAILROWS.
I can create more measures that use the logic of that table specified in the 'DetailRows Template' measure.
I can create even more measures that reuse the same logic but you get the idea.
Now I want to show that you can use the same DETAILROWS measure in a WINDOWS function as well for the Relation parameter where you have to specify the Table that will be used for PARTITION/ORDERBY etc.
So here is a Matrix which contains Category > Brand > Color and we want to create 3 measures that show Category Total, Brand Total, and Color Total at each level and each row.
So for the Category total we can write this code:
Similarly from Brand Total the code will be the following with just introduction of PARTITIONBY ( Products[Category] ) so that we get Grand total for each Brand & Color but for that category.
For the Color total we just need
PARTITIONBY ( Products[Category], Products[Brand] ) Now as you can see all 3 codes are reusing ALL ( Products[Category], ..... ) Instead of that we can store the logic in a Detail Rows Expression of a Measure.
Now we only need to replace ALL() with DETAILROWS ()
The UI throws error but the report works.
For the next example let's say we want to reuse the result of the [Products Columns] measure but only show few brands or colors in the matrix we are allowed to filter out this result by using FILTER & CALCULATETABLE and then we can use any other function for computing Sales Amount.
Now the amount is correct but it shows the same value at each color and brand the reason being the context transition on the colors is overriding the existing colors by red & blue so to intersect the filter context we can introduce the KEEPFILTERS functions.
Same can also be done with CALCULATE
In the next example I want to show that we can create virtual columns under Detail Rows Expression that can be used later on either in Window or other functions.
As usual I was having a stroke while thinking of a good name for the Detail Rows Measure for this use case so I went with Virtual Column DR
In the first example you can see we get the correct Profit, the code for Total Profit measure is [Total Sales] - [Total Cost], the DETAILROWS measure is slow because we are just complicating things but if we take that out of equation then we will see how useful it is.
By replacing [@Profit] with [@Sales] & [@Cost] you can return the corresponding measures.
Now let's say we want total of each continent at each Gender level, we can use WINDOW function and PARTITIONBY Customer[Continent]
If we want Running Total of Sales and Cost we can just PARTITION & ORDER BY relevant columns, but for this example I commented out all the column apart from Date Columns from the measure Virtual Columns DR as it was way too slow
If you ever want to visualize the result of your DETAILROWS Measure you use that in a Calculated Table or execute as query in DAX Studio or Tabular Editor.
If want to create a table that shows top months by each year I can reuse the logic using this code to create a calculated table:
If I want Top colors for each year I can write a measure like this one:
Which returns:
So by using the DETAILROWS you see how you can reuse the logic anywhere else in your report and create complex calcualtions that can't be done otherwise.
Also once you're done with development make sure you hide the measure with Detail Rows Expression or put the Expression as a comment in the same measure's DAX Expression so that end users can see what is the logic behind the Detail Rows Expression.
You can download the file from here: https://drive.google.com/file/d/1qp1goa024rsmslwzO1IZh03wpFQ4CzzK/view?usp=sharing