Dax formula measure

[Anonymous]

Hello, everyone,
I am creating a DAX measure to calculate the number for which the "START" intervention exists and for which at least one "END" intervention exists. You can see the columns below : The columns are from the same table called Table 1. according to this figure, there are a lot of data. 

My best regards. Thank you.

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Based on my understanding, you could create a measure like this

count for start = CALCULATE(COUNT(Sheet8[column2]),FILTER(ALLEXCEPT(Sheet8,Sheet8[column1]),Sheet8[column2]="start"))

count for end = CALCULATE(COUNT(Sheet8[column2]),FILTER(ALLEXCEPT(Sheet8,Sheet8[column1]),Sheet8[column2]="end"))

flag = IF([count for start]<>BLANK()&&[count for end]<>BLANK(),1,0)

count = COUNTX(FILTER(VALUES(Sheet8[column1]),[flag]=1),Sheet8[column1])

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi

could you please clarify? Start /end within the same Column1 value? 

So for example in this case your expected result would be 3?

IF SO use a calculated column with this formula

IsEndFound=

VAR thisCol1=Yourtablename['Column1']

RETURN
IF(YourTableName['Column2']="Start";
countrows(filter(yourtablename;
    yourtablename['Column1']=thisCol1 &&
    yourTableName['Column2']="End"))
;0)

This column will have a 1 for the START rows that has a corresponding END. Your result is the sum of this column.

CAREFUL: if there are MULTIPLE END with the same start you will have more than 1 in that cell!

[Anonymous]

Hi,

Sorry, I didn't mention it I would like to do a distinct count to calculate the number for which the "START" intervention exists and for which at least one "END" intervention exists.

[Anonymous]

Hi

then use this formula

IsEndFound=

VAR thisCol1=Yourtablename['Column1']

RETURN
IF(YourTableName['Column2']="Start";
countrows(filter(yourtablename;
    yourtablename['Column1']=thisCol1 &&
    yourTableName['Column2']="End"))
;BLANK())

This will return BLANK for rows where there is not corresponding END and so you can use a distinctcount

CALCULATE ( DISTINCTCOUNT ( MyTable[MyColumn] ), MyTable[MyColumn] <> BLANK () )
    + 0