Create Conditional Column with Two Dynamic Filter Parameter

[fab_rez_mil]

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).

[fab_rez_mil]

It worked just perfectly. Thanks a lot!

[johnt75 · Accepted 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