cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lotwijnants
Regular Visitor

How to combine filter, rowcount and earliest date

I have a dataset like the example:

 

ROW	ID	STATUS	Date Time
1	ABC001	RIGHT	6-2-2021 19:11
2	ABC001	RIGHT	7-2-2021 19:11
3	ABC001	RIGHT	8-3-2021 19:11
4	ABC001	LEFT	6-2-2021 19:11
5	ABC001	LEFT	7-2-2021 19:11
6	ABC001	LEFT	8-2-2021 19:11
7	ABC001	LEFT	9-2-2021 19:11
8	ABC001	LEFT	10-2-2021 19:11
9	ABC001	LEFT	11-2-2021 19:11
10	ABC001	LEFT	12-2-2021 19:11
11	ABC002	RIGHT	6-2-2021 19:11
12	ABC002	RIGHT	7-2-2021 19:11
13	ABC002	RIGHT	8-3-2021 19:11
14	ABC002	LEFT	6-2-2021 19:11
15	ABC002	LEFT	7-2-2021 19:11
16	ABC002	LEFT	8-2-2021 19:11
17	ABC002	LEFT	9-2-2021 19:11
18	ABC002	LEFT	10-2-2021 19:11
19	ABC002	LEFT	11-2-2021 19:11
20	ABC002	LEFT	12-2-2021 19:11
21	ABC003	RIGHT	6-3-2021 19:11
22	ABC003	RIGHT	7-3-2021 19:11
23	ABC003	RIGHT	8-3-2021 19:11
24	ABC003	RIGHT	9-5-2021 19:11
25	ABC003	LEFT	6-3-2021 19:11
26	ABC003	LEFT	7-3-2021 19:11
27	ABC003	LEFT	8-3-2021 19:11
28	ABC003	LEFT	9-3-2021 19:11
29	ABC003	LEFT	10-3-2021 19:11
30	ABC003	LEFT	11-3-2021 19:11
31	ABC004	RIGHT	6-3-2021 19:11
32	ABC004	RIGHT	7-3-2021 19:11
33	ABC004	RIGHT	8-3-2021 19:11
34	ABC004	RIGHT	9-5-2021 19:11
35	ABC004	RIGHT	6-3-2021 19:11
36	ABC004	LEFT	7-3-2021 19:11
37	ABC004	LEFT	8-3-2021 19:11
38	ABC004	LEFT	9-3-2021 19:11
39	ABC004	LEFT	10-3-2021 19:11
40	ABC004	LEFT	11-3-2021 19:11

 

 

What I want to do are a couple of steps:

  1. Filter the table on: STATUS = "RIGHT" (found how o do his seperatly: 

 

 

 

 

NEW TABLE 1 = FILTER(SAMPLE_DATASET, SAMPLE_DATASET[STATUS]="RIGHT")

 

 

2. per ID: determine earlies date time
something like: 

 

EARLIEST_DATE = Calculate(Min('NEW TABLE 1'[Date Time]), Filter('NEW TABLE 1', 'NEW TABLE 1'[ID]=Earlier([ID]))​

 

 

3. Per ID: Count rows = # STATUS (found how to do it seperately: 

 

NEW TABLE 2 = GROUPBY('NEW TABLE 1', 'NEW TABLE 1'[ID], "# STATUS", COUNTX(CURRENTGROUP(), 'NEW TABLE 1'[ID]))

 

 

4. Per Year and Month, count how many ID’s had the same '# STATUS' value = 'OCCURENCE'

 

So in the end I would like to end up with:

 

YEAR	MONTH	        # STATUS     OCCURRENCE
2021	February	    3	     2
2021	March	        4	     1
2021	March	        5	     1

 


I'm just not sure on how to combine all of the steps, and also step 2 and 4 with finding the first date within the
 ID group & calculating the OCCURENCE column per MONTH and YEAR

 

hope someone can help me 😄

1 ACCEPTED SOLUTION
amitchandak
Super User IV
Super User IV

@lotwijnants , Other than ID , date/date(which is max) , have all column as measures like

 

Measure =
VAR __id = MAX ('Table'[ID] )
VAR __date = CALCULATE ( MAX('Table'[Date] ), ALLSELECTED ('Table' ), 'Table'[ID] = __id )
CALCULATE ( max ('Table'[STATUS] ), VALUES ('Table'[ID] ),'Table'[ID] = __id,'Table'[Date] = __date )



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

6 REPLIES 6
Eyelyn9
Community Support
Community Support

Hi @lotwijnants ,

 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution to make the thread closed. More people will benefit from it.

 

Hope to hear from you😀

 

Best Regards,
Eyelyn Qin

Eyelyn9
Community Support
Community Support

Hi @lotwijnants ,

 

Please try this to create a new table:

Table =
VAR _T =
    FILTER ( 'SAMPLE_DATASET', [STATUS] = "RIGHT" )
RETURN
    DISTINCT (
        SELECTCOLUMNS (
            'SAMPLE_DATASET',
            "ID", [ID],
            "Year-Month", FORMAT ( [Date Time], "YYYY-MM" ),
            "# STATUS", COUNTX ( FILTER ( _T, [ID] = EARLIER ( [ID] ) ), [ROW] ),
            "OCCURRENCE",
                COUNTX (
                    FILTER (
                        _T,
                        [ID] = EARLIER ( [ID] )
                            && YEAR ( [Date Time] ) = EARLIER ( [Date Time].[Year] )
                            && MONTH ( [Date Time] ) = EARLIER ( [Date Time].[MonthNo] )
                    ),
                    [ROW]
                )
        )
    )

The final output is shown below:

count by ID.PNG

Actually, I'm a little confused about the step2 you mentioned and the logic of OCCURRENCE column you want to get...

If my method does not make sense, please share more detail information about the logics to help me clarify your scenario.

 

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

Hi Evelyn, this would have worked for me 🙂 I tried it just now and it worked:D I did end up with a different solution as I had to create the dashboard a little sooner

Jihwan_Kim
Community Champion
Community Champion

Hi, @lotwijnants 

Please correct me if I wrongly understood your question.

Please check the below measures and the sample pbix file's link down below.

 

Status Count =
VAR currentmonth =
MAX ( Dates[Month] )
VAR currentyear =
MAX ( Dates[Year] )
VAR currentid =
MAX ( 'Table'[ID] )
VAR statusright =
FILTER (
ALL ( 'Table' ),
'Table'[STATUS] = "RIGHT"
&& MONTH ( 'Table'[Date] ) = currentmonth
&& YEAR ( 'Table'[Date] ) = currentyear
)
RETURN
COUNTROWS ( statusright )
 
Occurance Count =
VAR currentmonth =
MAX ( Dates[Month] )
VAR currentyear =
MAX ( Dates[Year] )
VAR currentid =
MAX ( 'Table'[ID] )
VAR statusright =
GROUPBY (
FILTER ( ALL ( 'Table' ), 'Table'[STATUS] = "RIGHT" ),
'Table'[ID],
"@mintime", MINX ( CURRENTGROUP (), 'Table'[Date] )
)
VAR filterstatusright =
FILTER (
statusright,
MONTH ( [@mintime] ) = currentmonth
&& YEAR ( [@mintime] ) = currentyear
)
RETURN

COUNTROWS ( filterstatusright )
 
 

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

amitchandak
Super User IV
Super User IV

@lotwijnants , Other than ID , date/date(which is max) , have all column as measures like

 

Measure =
VAR __id = MAX ('Table'[ID] )
VAR __date = CALCULATE ( MAX('Table'[Date] ), ALLSELECTED ('Table' ), 'Table'[ID] = __id )
CALCULATE ( max ('Table'[STATUS] ), VALUES ('Table'[ID] ),'Table'[ID] = __id,'Table'[Date] = __date )



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

@amitchandak thank you for your reply. I have just started with power BI, so sorry this question that might be a bit stupid: untill now I only have used measurements to calculate 1 variable, like: just Measure =
VAR __id = MAX ('Table'[ID] ). You are saying you can have multiple measurements in 1 right? When I tru this out in powerBI its says: The syntax for 'CALCULATE' is incorrect.

Or do you mean, to make a measure of each of the ones that you summed?

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors