How to combine filter, rowcount and earliest date

[Anonymous]

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 😄

[Anonymous]

Hi @Anonymous ,

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

[Anonymous]

Hi @Anonymous ,

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:

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.

[Anonymous]

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]

Hi, @Anonymous 

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 )

https://www.dropbox.com/s/ewz3vqcw3ek9kyt/lotwijnants.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

[amitchandak · Accepted solution]

@Anonymous , 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 )

[Anonymous]

@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?