Forum Discussion
How do you FILTER by ID match with a temporary table
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
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/1850540#M714793
Similar Issues:
https://community.powerbi.com/t5/Desktop/DAX-query-to-compare-a-value-in-one-table-to-see-if-it-exists-in/td-p/56033
https://community.powerbi.com/t5/Desktop/INNER-JOIN/m-p/258597
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.
10 Replies
- amitchandak
Super User
Anonymous , 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)) - jdbuchanan71
Anonymous
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)- jdbuchanan71
When you say grouped by datetime I'm not sure what you mean. Can you explain?
- 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 - jdbuchanan71
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.
