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.
Hi, I have a data set similar to below:
Name | Income Type | Amount |
Alex | Initial | 100 |
John | Initial | 100 |
Sam | Ongoing | 100 |
Alex | Initial | 100 |
John | Ongoing | 100 |
Sam | Ongoing | 100 |
I am trying to create a measure that counts the number of people that just have 'Initial' i.e. have initial but don't have ongoing. Therefore on the above, it would be 1 which would be Alex.
Any help would be much appreciated.
Many thanks in advance,
Alex
Solved! Go to Solution.
Measure = IF( CALCULATE(DISTINCTCOUNT(Table1[Column]) = 1), CALCULATE(DISTINCTCOUNT(Table1[Column]), Table1[Income Type] = "Initial"), BLANK() )
Table1[Column] being a Calculated column = [Name] & [Income Type]
@tex628 ,
The solution of Tex is simple and great. In addtion, you can also create a calculate column [Flag]:
Flag = IF ( CALCULATE ( DISTINCTCOUNT ( 'Table'[Income Type] ), ALLEXCEPT ( 'Table', 'Table'[Name] ) ) = 1 && CALCULATE ( DISTINCTCOUNT ( 'Table'[Name] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[Name] ), 'Table'[Income Type] = "Initial" ) ) = 1, 1, 0 )
Then create a measure which may also achieve the result:
Count = CALCULATE(DISTINCTCOUNT('Table'[Name]), FILTER('Table', 'Table'[Flag] = 1))
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@tex628 ,
The solution of Tex is simple and great. In addtion, you can also create a calculate column [Flag]:
Flag = IF ( CALCULATE ( DISTINCTCOUNT ( 'Table'[Income Type] ), ALLEXCEPT ( 'Table', 'Table'[Name] ) ) = 1 && CALCULATE ( DISTINCTCOUNT ( 'Table'[Name] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[Name] ), 'Table'[Income Type] = "Initial" ) ) = 1, 1, 0 )
Then create a measure which may also achieve the result:
Count = CALCULATE(DISTINCTCOUNT('Table'[Name]), FILTER('Table', 'Table'[Flag] = 1))
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Measure = IF( CALCULATE(DISTINCTCOUNT(Table1[Column]) = 1), CALCULATE(DISTINCTCOUNT(Table1[Column]), Table1[Income Type] = "Initial"), BLANK() )
Table1[Column] being a Calculated column = [Name] & [Income Type]
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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |