How do you FILTER by ID match with a temporary table

[moosepng]

Hi,

Running into a bit of trouble with this one. I've posted about a similar issue before, so will link that at the bottom.

I want to create a measure (or column) that calculates the number of people where a condition is met, and where they have previously ever met a seperate condition. I also want to be able to group results by DateTime.

I have some dummy data '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

So, in rough logic/code I want to

CALCULATE the DISTINCTCOUNT of ID where exit = 1 and where 'Table'[ID] = 'temp_table'ID (when temp_table = FILTER('Table', 'Table'[Status_A] = "A"))

I thought the simplest way to do this would be to use a temp_table VAR, similar to a CTE in SQL, adding the clause - if 'Table'[ID] = 'temp_table' ID when temp_table = FILTER('Table', 'Table'[Status_A] = "A")

ID	Status_A
1001	A

So...

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

My expected result is 1.
There is 1 ID that has previously had a Status_A = A and has EXIT = 1.
This one record has an ID 1001 and occured on the 5/02/2021 12:00.

My actual result is BLANK

As user Jihwan_Kim pointed out, I can get 1 by summing my measure:

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

But this is not able to be grouped by DateTime.

Previous post:
https://community.powerbi.com/t5/Desktop/DISTINCTCOUNT-of-ID-WHERE-a-previous-condition-is-TRUE/m-p/...

Similar Issues:
https://community.powerbi.com/t5/Desktop/DAX-query-to-compare-a-value-in-one-table-to-see-if-it-exis...
https://community.powerbi.com/t5/Desktop/INNER-JOIN/m-p/258597

[jdbuchanan71 · Accepted solution]

Yep, you can add a column to your table to assign the correct DateTime to each row for the ID like this.

Exit DateTime = 
CALCULATE (
    MAX ( 'Table'[Date] ),
    ALLEXCEPT ( 'Table', 'Table'[ID] ),
    'Table'[Exit] = 1
)

Then you use that column to group the measure.

[jdbuchanan71]

OK, but your Status A and Exit rows have differnt datetime for the same ID.  How do you pick which one they should be grouped by?

DateTime	ID	Status_A	Entry	Exit
25/10/2020 14:25	1001	A
29/10/2020 14:25	1001		1	0
5/2/2021 12:00	1001		0	1

[moosepng]

Good point.
I want to know the number of IDs who exit (Exit = 1) who ever had a Status_A = A at the time of exit.
If you can help me articulate this it would be much appriciated 🙂

[moosepng]

Can't post the solution becuase of post flooding? i'll try a pic

[jdbuchanan71]

@moosepng 

Not sure I am understanding what you are looking for but would something like this work?

Measure = 
VAR _Entry = CALCULATETABLE(VALUES('Table'[ID]),'Table'[Entry] = 1)
VAR _Status = CALCULATETABLE(VALUES('Table'[ID]),'Table'[Status] = "A")
VAR _Combine = INTERSECT(_Entry,_Status)
RETURN
COUNTROWS(_Combine)

[moosepng]

Looks like the measure is correct, but it can't be grouped by datetime

[jdbuchanan71]

When you say grouped by datetime I'm not sure what you mean.  Can you explain?

[moosepng]

Sorry. So the original table is by DateTime. My expected result is

I want to know the Calculation (DISTICTCOUNT of ID) by Date (DateTime)

[amitchandak]

@moosepng , Try a measure like

calculate(distinctcount(Table[ID]), filter(Table, Table[ID] = calculate(max(Table[ID] ), filter(allselected(table), table[ID] = max(Table[ID]) && 'Table'[Status_A] = "A"))
&& Table[Exit] =1))

[moosepng]

Works, but no total and can't be grouped by datetime