Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a table ‘RmR (unik)’ with the primary key information [ins_no], which corresponds to the installation number of a product. In this table, I also have the column with the name of the sales expert who is responsible for the installation in the column [Name], and the corresponding commercial year/month in which the installation was executed, information given in the column [comm-MonthTM]. The format of the column [comm-MonthTM] is text, with its values being the corresponding concatenation of the year and month number, e.g., 202202 (Year = 2022, month = 02). It is possible to see the table below for better understanding.
I want to create a conditional column in this table ‘RmR (unik)’, which gives me for each row the “count of installations a salesperson did in the corresponding commercial year/month”. Note that the names in the column name repeats, as it is not a unique value, since sales expert can do more than one installation. I want to this calculation to be dynamic, that is, the row will do the calculation with its corresponding commercial year/month column [comm-MonthTM] and sales expert column [Name].
I have tried to do the following created column, doing a filter using two different related tables ‘Dim_Names’ which is the table with all the sales expert names (Primary Key ) and ‘Date (2)’, which is a table with the corresponding days and commercial year/month.
Total Inst = calculate(
COUNT('RmR (unik)'[ins_no]),
filter(Dim_Names, Dim_Names[Name] = 'RmR (unik)'[Name]),
filter('RmR (unik)', 'RmR (unik)'[comm-MonthTM] = RELATED('Date (2)'[Commercial month]))
)
However, the result in the created column [Total Inst] corresponds to the total count of installations a sales expert did in the whole data source, that is, it is not filtering by the corresponding commercial year/month column, as you can see below. The
In the image below I changed in the DAX formula the commercial year/month for a specific text instead, defined as “202202”. In this case it filters correctly for the month of Feb 2022, only. But I want to be correct for the other commercial year/month too.
Total Inst = calculate(
COUNT('RmR (unik)'[ins_no]),
filter(Dim_Names,Dim_Names[Name] = 'RmR (unik)'[Name]),
filter('RmR (unik)', 'RmR (unik)'[comm-MonthTM] = "202202")
)
For example, for the sales expert “highlighted in red” the calculated amount is correct for the commercial year/month 0f "202202" (feb), but not for "202203" (mar).
Solved! Go to Solution.
If it needs to be a calculated column then the below should work I think
Total inst =
var currentSalesPerson = 'RmR (unik)'[Name]
var currentMonth = 'RmR (unik)'[comm-MonthTM]
var result = CALCULATE( COUNTROWS( 'RmR (unik)')
REMOVEFILTERS('RmR (unik)'),
TREATAS( { ( currentSalesPerson, currentMonth ) }, 'RmR (unik)'[Name], 'RmR (unik)'[comm-MonthTM])
)
return result
It might be better as a measure, in which case the first 2 variables would need to use SELECTEDVALUE on the appropriate columns
It worked just perfectly. Thanks a lot!
If it needs to be a calculated column then the below should work I think
Total inst =
var currentSalesPerson = 'RmR (unik)'[Name]
var currentMonth = 'RmR (unik)'[comm-MonthTM]
var result = CALCULATE( COUNTROWS( 'RmR (unik)')
REMOVEFILTERS('RmR (unik)'),
TREATAS( { ( currentSalesPerson, currentMonth ) }, 'RmR (unik)'[Name], 'RmR (unik)'[comm-MonthTM])
)
return result
It might be better as a measure, in which case the first 2 variables would need to use SELECTEDVALUE on the appropriate columns
User | Count |
---|---|
94 | |
86 | |
78 | |
69 | |
63 |
User | Count |
---|---|
114 | |
99 | |
97 | |
65 | |
59 |