I need to find max id with condition

[amirzayev13]

HI, I have a table F_JOB_STATUS:my task:

A/B*100

A = cases ([case_id]) (only [case_type_id] = 1 or 2) solved (and([case_status_id] = 7 , [de_id] <> BLANK())) in shorter period ([WTF] = 1) during that month

B = All solved cases (and([case_status_id] = 7 , [de_id] <> BLANK())) 

As you can see, the id in the table is duplicated, you need to take into account the unique ones (for this I think you can find the maximum id, like so: MAX([ID] over [CASE_ID]))

To find the maximum id, I created a column

OPEN_C&C_WTF_MAX (F_JOB_Status) = 
CALCULATE(MAX(F_JOB_STATUS[ID]),FILTER(F_JOB_STATUS,F_JOB_STATUS[JOB_ID]=EARLIER(F_JOB_STATUS[JOB_ID])))

but it did not help me, since it is a column and when filtering by date it loses its meaning

Is it possible to create 2 measures A and B, in which conditions A and B are taken into account and the maximum id is taken into account?
or maybe there is another way?

pbix file

[v-diye-msft · Accepted solution]

Hi @amirzayev13 

Sorry for my late reply. I’ve modified the measure:

MeasureA = CALCULATE(MAX(F_JOB_STATUS[ID]),FILTER(ALL(F_JOB_STATUS),[Column]=1&&[CASE_STATUS_ID]=7&&[DE_ID]<>BLANK()&&[WTF]=1),VALUES(F_JOB_STATUS[CASE_STATUS_ID]),VALUES(F_JOB_STATUS[CASE_ID]))
MeasureB = CALCULATE(MAX([ID]),FILTER(ALL(F_JOB_STATUS),[CASE_STATUS_ID]=7&&[DE_ID]<>BLANK()),VALUES(F_JOB_STATUS[CASE_STATUS_ID]),VALUES(F_JOB_STATUS[CASE_ID]))

Kindly check if it works.

Best regards,

Dina Ye

[amirzayev13]

---

@amirzayev13 wrote:

HI, I have a table F_JOB_STATUS:my task:

A/B*100

A = cases ([case_id]) (only [case_type_id] = 1 or 2) solved (and([case_status_id] = 7 , [de_id] <> BLANK())) in shorter period ([WTF] = 1) during that month

B = All solved cases (and([case_status_id] = 7 , [de_id] <> BLANK())) 

As you can see, the id in the table is duplicated, you need to take into account the unique ones (for this I think you can find the maximum id, like so: MAX([ID] over [CASE_ID]))

To find the maximum id, I created a column

OPEN_C&C_WTF_MAX (F_JOB_Status) = 
CALCULATE(MAX(F_JOB_STATUS[ID]),FILTER(F_JOB_STATUS,F_JOB_STATUS[JOB_ID]=EARLIER(F_JOB_STATUS[JOB_ID])))

but it did not help me, since it is a column and when filtering by date it loses its meaning

Is it possible to create 2 measures A and B, in which conditions A and B are taken into account and the maximum id is taken into account?
or maybe there is another way?

pbix file

---

Is there really no solution? 

[v-diye-msft]

Hi @amirzayev13 

Here’re some points need your further confirmation:

1. Please specify the [DE_ID],  I figure it’s [ID] in the table. please correct the formula below if I go wrong
2. B = CASE_ID conditions ([CASE_STATUS_ID] <> 7 and ( [CASE_STATUS_ID] = 7 and [DE_ID] is null)). The logic is contradictory under the condition. I figure the former one is [CASE_TYPE_ID], please correct the formula below if I go wrong.
3. You’d like to find the maximum [CASE_ID], not [ID] in the table.

I created a column:

Column = IF([CASE_TYPE_ID]=1||[CASE_TYPE_ID]=2,1)

Then use below measures:

A = CALCULATE(MAX(F_JOB_STATUS[CASE_ID]),FILTER(F_JOB_STATUS,[WTF]=1&&[CASE_STATUS_ID]=7&&NOT(ISBLANK([ID]))&&[Column]=1))
B = CALCULATE(MAX(F_JOB_STATUS[CASE_ID]),FILTER(F_JOB_STATUS,[CASE_TYPE_ID]<>7&&[CASE_STATUS_ID]=7&&NOT(ISBLANK([ID]))))

Measure = [A]/[B]*100

Results shown as below:

Pbix attached here for your reference: https://wicren-my.sharepoint.com/:u:/g/personal/dinaye_wicren_onmicrosoft_com/ETusXrQDeNhGuiAHOK7MeY...

Best regards,

Dina Ye

[amirzayev13]

Here’re some points need your further confirmation:

1. Please specify the [DE_ID],  I figure it’s [ID] in the table. please correct the formula below if I go wrong
2. B = CASE_ID conditions ([CASE_STATUS_ID] <> 7 and ( [CASE_STATUS_ID] = 7 and [DE_ID] is null)). The logic is contradictory under the condition. I figure the former one is [CASE_TYPE_ID], please correct the formula below if I go wrong.
3. You’d like to find the maximum [CASE_ID], not [ID] in the table.

Hi @v-diye-msft ,
Thank you for helping me, I am very grateful.

clarifying:

1. [DE_ID] - this is a different ID. forgot to specify in the table, sorry

2.

A = cases ([case_id]) (only [case_type_id] = 1 or 2) solved (and([case_status_id] = 7 , [de_id] <> BLANK())) in shorter period ([WTF] = 1) during that month

B = All solved cases (and([case_status_id] = 7 , [de_id] <> BLANK())) 

I need to find the max [ID] over [CASE_ID], because this is the only way to calculate the status of work at a certain moment. Since [CASE_ID] is duplicated and their statuses are different, you need to select one. And this is possible with max [ID].

Modified data and added expected result.

excuse me for my English, I hope I wrote it clearly.

[v-diye-msft]

Hi,

Please try below ones:

Add a column : 

Column = IF([CASE_TYPE_ID]=1||[CASE_TYPE_ID]=2,1)

then

MeasureA = CALCULATE(MAX(F_JOB_STATUS[ID]),FILTER(F_JOB_STATUS,[Column]=1&&[CASE_STATUS_ID]=7&&[DE_ID]<>BLANK()&&[WTF]=1))

MeasureB = CALCULATE(MAX([ID]),FILTER(F_JOB_STATUS,[CASE_STATUS_ID]=7&&[DE_ID]<>BLANK()))

Best regards,

Dina Ye

[amirzayev13]

---

@v-diye-msft wrote:

Hi,

Please try below ones:

Add a column : 

Column = IF([CASE_TYPE_ID]=1||[CASE_TYPE_ID]=2,1)

then

MeasureA = CALCULATE(MAX(F_JOB_STATUS[ID]),FILTER(F_JOB_STATUS,[Column]=1&&[CASE_STATUS_ID]=7&&[DE_ID]<>BLANK()&&[WTF]=1))

MeasureB = CALCULATE(MAX([ID]),FILTER(F_JOB_STATUS,[CASE_STATUS_ID]=7&&[DE_ID]<>BLANK()))

Best regards,

Dina Ye

---

This is not what I did as you wrote (I add a pbix file), but as you can see from the photo below, the case_id is duplicated, and measure A and B gives a copy of the [ID].  And I need a maximum [ID] over [CASE_ID]. Then to calculate Distinctcount[measureA] / distinctcount[measureB] * 100

[amirzayev13]

Hi @v-diye-msft ,

MAX (F_JOB_Status) column = 
CALCULATE(MAX(CASE_STATUS[ID]), FILTER(F_JOB_STATUS, F_JOB_STATUS[CASE_ID]=EARLIER(F_JOB_STATUS[CASE_ID])))

Here in this form, the code works fine, but only when creating a column. This code in this form does not work with measure.
I need exactly the measure, because the column is calculated when the data is updated 1 time. A measure takes into account filters

[amirzayev13]

---

Here’re some points need your further confirmation:

1. Please specify the [DE_ID],  I figure it’s [ID] in the table. please correct the formula below if I go wrong
2. B = CASE_ID conditions ([CASE_STATUS_ID] <> 7 and ( [CASE_STATUS_ID] = 7 and [DE_ID] is null)). The logic is contradictory under the condition. I figure the former one is [CASE_TYPE_ID], please correct the formula below if I go wrong.
3. You’d like to find the maximum [CASE_ID], not [ID] in the table.

clarifying:

1. [DE_ID] - this is a different ID. forgot to specify in the table, sorry

2.

A = cases ([case_id]) (only [case_type_id] = 1 or 2) solved (and([case_status_id] = 7 , [de_id] <> BLANK())) in shorter period ([WTF] = 1) during that month

B = All solved cases (and([case_status_id] = 7 , [de_id] <> BLANK())) 

I need to find the max [ID], because this is the only way to calculate the status of work at a certain moment. Since [CASE_ID] is duplicated and their statuses are different, you need to select one. And this is possible with max [ID].

Modified data and added expected result.

excuse me for my English, I hope I wrote it clearly.

[amirzayev13]

measure = MAXX(
	ADDCOLUMNS(
		ALL(
		F_JOB_STATUS[ID]), "Unique_ID", 
			CALCULATE(
				FILTER(F_JOB_STATUS, 
					AND(
					F_JOB_STATUS[CASE_STATUS_ID]=7, 
						AND(
						F_JOB_STATUS[WTF]=1, 
							OR(F_JOB_STATUS[CASE_TYPE_ID]=1, F_JOB_STATUS[CASE_TYPE_ID]=2)))))), 
								FILTER(
									F_JOB_STATUS, F_JOB_STATUS[DE_ID] <> BLANK()))

and it did not help, already the 2nd day I am looking for an answer .....

[v-diye-msft · Accepted solution]

Hi @amirzayev13 

Sorry for my late reply. I’ve modified the measure:

MeasureA = CALCULATE(MAX(F_JOB_STATUS[ID]),FILTER(ALL(F_JOB_STATUS),[Column]=1&&[CASE_STATUS_ID]=7&&[DE_ID]<>BLANK()&&[WTF]=1),VALUES(F_JOB_STATUS[CASE_STATUS_ID]),VALUES(F_JOB_STATUS[CASE_ID]))
MeasureB = CALCULATE(MAX([ID]),FILTER(ALL(F_JOB_STATUS),[CASE_STATUS_ID]=7&&[DE_ID]<>BLANK()),VALUES(F_JOB_STATUS[CASE_STATUS_ID]),VALUES(F_JOB_STATUS[CASE_ID]))

Kindly check if it works.

Best regards,

Dina Ye

[amirzayev13 · Accepted solution]

@v-diye-msft 

Thank you very much.
I solved the problem differently, literally an hour ago.

measureA = 
calculate(
	distinctcount( F_CASE_STATUS[case_id] ), 
	 F_JOB_STATUS[CASE_TYPE_ID] in {1,2},
	 F_JOB_STATUS[CASE_STATUS_ID] = 7,
	 F_JOB_STATUS[DE_ID] <> BLANK(),
	 F_JOB_STATUS[WTF] = 1)

measureB = 
calculate(	
	distinctcount( F_JOB_STATUS[case_id] ),
	 F_JOB_STATUS[CASE_STATUS_ID] = 7,
	 F_JOB_STATUS[DE_ID] <> BLANK())

Final Measure = 
DIVIDE([A], [B])