DISTINCTCOUNT of ID WHERE a previous condition is TRUE

[moosepng]

I have a Table (Table)

DateTime	ID	Status_A	Entry	Exit
25/10/2020 14:25	1001	A
29/10/2020 14:25	1001		1	0
1/12/2020 15:30	1002		1	0
5/02/2021 12:00	1001		0	1
15/04/2021 21:45	1002		0	1

The table records status changes of various types by DateTime.

I want to calculate the number (distinct count of clients) who have exited (exit = 1) where the client has previously had a Status_A (Status_A = 1)

Something like:

Measure = 
VAR select_id = SELECTEDVALUE('Table'[ID])
VAR temp_table = FILTER('Table', 'Table'[Status_A] = "A")
RETURN
IF(
    CONTAINS(temp_table, 'Table'[ID], select_id),
    CALCULATE(
        DISTINCTCOUNT('Table'[ID]), 
        'Table'[Exit] = 1),
    BLANK()
)

There is 1 client (ID=1001) who has exited (Exit = 1) and who previously had a Status A (Status_A = "A") so I expect Measure = 1.
However, Measure = BLANK

Oddly, when I create a table with ID and Measure the data looks correct, except for the Total

Why is the total not 1? My guess is the data type.
Is there a correct way to structure the IF statement in my measure to return a value that can be totaled/averaged etc.?

I've looked at the Microsoft documentation, but I can't find a way to strucutre the IF statement to return a number that can be calculated into a total.

[moosepng]

That works :), but it's not ideal.
I have to create a number of measures with this logic and this would double the number of measures in my report.
Do you know what the difference is?

If I include your solution in the orignal measure it has the same problem.. is it a bug?

Measure test fix 2 = 
VAR select_id = SELECTEDVALUE('Table'[ID])
VAR temp_table = FILTER('Table', 'Table'[Status_A] = "A")
RETURN
IF(
    CONTAINS(temp_table, 'Table'[ID], select_id),
    SUMX( VALUES('Table'[ID]), 
        CALCULATE(
            DISTINCTCOUNT('Table'[ID]), 
            'Table'[Exit] = 1)
    ),
    BLANK()
)

[v-kkf-msft · Accepted solution]

Hi @moosepng ,

Try the following formula:

Measure = 
var exit_value = 
CALCULATE(
    MAX('Table'[Exit]),
    FILTER(
        ALL('Table'),
        'Table'[ID] = MAX('Table'[ID])
    )
)
return 
CALCULATE(
    DISTINCTCOUNT('Table'[ID]),
    FILTER(
        'Table',
        exit_value = 1
        && 'Table'[Status_A] = "A"
    )
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[moosepng]

How would it work with text values in 'Table'[Exit] ?

DateTime	ID	Status_A	Entry	Exit
25/10/2020 14:25	1001	A
29/10/2020 14:25	1001		Yes	No
1/12/2020 15:30	1002		Yes	No
5/02/2021 12:00	1001		No	Yes
15/04/2021 21:45	1002		No	Yes

[v-kkf-msft]

Hi @moosepng ,

If there are only two texts: "Yes" and "No", then just change "1" to "Yes" in the formula.

Because when comparing "Yes" and "No", it will be based on the order of the first letter, that is, "Yes" > "No".

Measure = 
var exit_value = 
CALCULATE(
    MAX('Table'[Exit]),
    FILTER(
        ALL('Table'),
        'Table'[ID] = MAX('Table'[ID])
    )
)
return 
CALCULATE(
    DISTINCTCOUNT('Table'[ID]),
    FILTER(
        'Table',
        exit_value = "Yes"
        && 'Table'[Status_A] = "A"
    )
)

Best Regards,
Winniz

[moosepng]

[moosepng]

Yep that looks like it gives the right result.
thanks

[Jihwan_Kim]

Hi, @moosepng 

Please try the below.

Measure Total Fix =

SUMX( VALUES('Table'[ID]), [Measure])

https://www.dropbox.com/s/15cxmwzovvaovjg/moosepngv2.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM