@legendsfan
Sure. It's probably easier to use the first measure:
DistinctCount of Owners with Only Managed =
CALCULATE (
DISTINCTCOUNT ( Data[Owner] ),
FILTER (
VALUES ( Data[Owner] ),
AND (
CALCULATE ( HASONEVALUE ( Data[Collaborator Type] ) ),
FIRSTNONBLANK ( Data[Collaborator Type], 0 ) = "Managed"
)
)
)In words, the measure is calculating a distinct count of Owner, just for those Owners that
- Have a single Collaborator Type
- And that Collaborator Type is "Managed"
The way this is achieved in DAX is:
- We use FILTER to iterate through the list of Owners in the current filter context, i.e. VALUES ( Data[Owner] ), and keep only those Owners that meet two conditions:
- The two conditions are contained in the AND function:
- CALCULATE ( HASONEVALUE ( Data[Collaborator Type] ) ): The owner must have exactly one Collaborator Type.
HASONEVALUE does what it sounds like: returns TRUE if the column specified has one value in the current filter context. Technical point: we need to wrap it in a CALCULATE in order to convert the row context (current row's Owner) to filter context (if we didn't do that, it would look at the entire table in the original filter context, without filtering based on the current row's Owner). - FIRSTNONBLANK ( Data[Collaborator Type], 0 ) = "Managed": The first Collaborator Type found for the current Owner must be "Managed".
FIRSTNONBLANK is a handy function for getting a single "first" value present in a column in the current filter context (based on lexical/numerical ordering). Since we only care about cases where there is a single Collaborator Type, we might as well just get the first Collaborator Type we find and see if it is "Managed".
See this page for more description of this use of FIRSTNONBLANK.
Note: CALCULATE is not required for FIRSTNONBLANK as it has built in context transition. - If the above two conditions are met, the current Owner is kept, otherwise it is rejected.
- Now that FILTER has reduced the list of Owners down to just those meeting the two conditions, CALCULATE overwrites the filter context on the Owner column to this filtered list of Owners.
- The first argument of CALCULATE, i.e. DISTINCTCOUNT ( Data[Owner] ), is then evaluated in this modified context. In fact it is just the number of Owners in the filtered list.
On your bonus question:
Where you have two 'conditions' within existing measures, and you want to use the intersection of them as a filter argument in another measure, you could rewrite both conditions as separate arguments within CALCULATE in the new measure. Multiple filter arguments within CALCULATE are always intersected.
(Note: You could also combine the conditions using other functions, such as within a single FILTER or using the INTERSECT function, if the conditions relate to the same column or set of columns.)
Unfortunately there isn't a simple way (without rewriting) of simply taking two existing measures and automatically combining filter contexts that occur within the evaluation of the original measures.
As a dummy example, let's say we want to filter Owners as in the above measure, but include them only if they are in the top 3 in terms of row count in the table. For those owners, we then want to calculate the total row count in the table.
The measure could be:
Row Count of Owners with Only Managed who are also in Top 3 by Row Count =
CALCULATE (
COUNTROWS ( Data ),
// First condition (list of Owners)
FILTER (
VALUES ( Data[Owner] ),
AND (
CALCULATE ( HASONEVALUE ( Data[Collaborator Type] ) ),
FIRSTNONBLANK ( Data[Collaborator Type], 0 ) = "Managed"
)
),
// Second condition (list of Owners)
TOPN ( 3, VALUES ( Data[Owner] ), CALCULATE ( COUNTROWS ( Data ) ) )
)
Cheers,
Owen 🙂
