Forum Discussion
How to merge two measures
Hello everyone,
My table follows this structure:
| Items | Code | Classification |
| XXX | 5 | D |
| XXX | 3 | D |
| YYY | 3 | H |
| ZZZ | 2 | D |
| ZZZ | 1 | K |
| ZZZ | 2 | D |
I have two measures:
* measure1 is a measure that brings me a DISTINCT count of all Items that have a K or H or R. rating (only for H classification, counting is only done if the Code is not 3)
measure1 = CALCULATE(DISTINCTCOUNT(table[Items]),FILTER(table, IF(SEARCH("K",table[Classification],,0) && NOT( SEARCH("3",table[Code],,0)),TRUE,FALSE) || SEARCH("H", table[Classification],,0 || SEARCH("R", table[Classification],,0) ),TRUE,FALSE)))
*measure2 is a measure that counts all the DISTINCT Items in my table that have codes 1 or 2 or 3.
measure2 = CALCULATE(DISTINCTCOUNT(table[Items]),FILTER(table, IF(table[Code]="1" || table[Code]="2" || table[Code]="3" ,TRUE,FALSE)))
I want to merge these two measures in way that the conditions of measure2 can be performed ONLY on the Items brought by measure1. Based on the example table above, the new measeure should bring me the value 1, cause only the ZZZ Item match the two measures.
Can somebody help me?
hi Anonymous
for mergedmeasure, you could try this simple logic:
MergedMeasure = CALCULATE(DISTINCTCOUNT('Table'[Items]),FILTER('Table',[measure1]*[measure2]<>BLANK()))By the way, your measure1 is wrong, you could use DataZoe's measure1 instead of yours.
Regards,
Lin
2 Replies
- DataZoe
Microsoft Employee
Anonymous I think this is what you are looking for:
Combo Measure =
IF ( ISBLANK ( [Measure 2] ), BLANK (), [Measure 1] )I also redid your Measure 1 and Measure 2, what you had typed didn't work for me.
Measure 1 =
CALCULATE (
DISTINCTCOUNT ( 'Table'[Items] ),
FILTER (
'Table',
'Table'[Classification] IN { "K", "R" }
|| ( 'Table'[Classification] = "H"
&& 'Table'[Code] <> 3 )
)
)Measure 2 =
CALCULATE (
DISTINCTCOUNT ( 'Table'[Items] ),
FILTER ( 'Table', 'Table'[Code] IN { 1, 2, 3 } )
)
v-lili6-msftCommunity Support
hi Anonymous
for mergedmeasure, you could try this simple logic:
MergedMeasure = CALCULATE(DISTINCTCOUNT('Table'[Items]),FILTER('Table',[measure1]*[measure2]<>BLANK()))By the way, your measure1 is wrong, you could use DataZoe's measure1 instead of yours.
Regards,
Lin
