Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a data set with Owner, Collaborator Type, and Folder. Owners can own multiple folders and each folder has a Collaborator Type of external or managed. I'd like to be able to get a distinct count of the number of owners 3 different ways.
Right now I can sum the Distinct value of Owner+Managed or Owner+External but no idea how to count the sum of Owners that ONLY do one type or the other. Any help is greatly appreciated.
Solved! Go to Solution.
It looks like you've added a calculated column with that code.
The code is actually a measure definition, and won't make sense in a calculated column.
Here's a sample pbix file using your sample table
Hi @legendsfan
So you want a distinct count of Owners that have exactly one Collaborator Type and that Collaborator Type = "Managed" (and the same for "External")?
My first thought would be to do something like this:
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" ) ) )
or equivalently
DistinctCount of Owners with Only Managed = COUNTROWS ( FILTER ( VALUES ( Data[Owner] ), AND ( CALCULATE ( HASONEVALUE ( Data[Collaborator Type] ) ), FIRSTNONBLANK ( Data[Collaborator Type], 0 ) = "Managed" ) ) )
Let me know if that is of any use.
Owen 🙂
I don't believe this code is quite right based on the sample table I did a test on. In this case I'd expect the number to be 2 since only Daisy and Edward have folders that are exclusively Managed. Bob has Managed folders but he also has an External folder so he should not be counted. Thoughts?
It looks like you've added a calculated column with that code.
The code is actually a measure definition, and won't make sense in a calculated column.
Here's a sample pbix file using your sample table
This is really helpful. Can you/anybody please help me with a similar but slightly different scenario where I want to identify (for simplicity's sake I will use the same example) the number of unique Owner's who have both a "Managed" AND "External" Collaborator Type? Thanks in advance - I'm very new to PowerBI! Cheers.
This was so helpful. Any chance you could explain the logic to me in simpleton terms? I'm new(ish) to reporting and trying to wrap my head around this. In this case I couldn't even sketch it up how I'd retrieve this data.
Bonus question. Is there an easy way to combine two measures and just use the item(s) that appear in both results?
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
The way this is achieved in DAX is:
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 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
81 | |
70 | |
62 |
User | Count |
---|---|
148 | |
116 | |
104 | |
90 | |
65 |