Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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 😄
Solved! Go to 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 )
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
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.
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
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.
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
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
@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 )
@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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |