Count column, filtered by measure

[thinker_02]

I want to create a measure to count Product ID where Status on latest date is high.

Product ID is a column.

Status on latest date is a measure.

Snapshot of the Profit table:

| Product ID | Date | Status |
|------------|------------|--------|
| ID1 | 4/4/2022 | Low |
| ID2 | 8/1/2022 | High |
| ID1 | 10/12/2022 | High |
| ID1 | 2/1/2022 | Medium |
| ID2 | 1/1/2023 | Low |
| ID2 | 6/7/2022 | Medium |
| ID1 | 7/7/2023 | Medium |

Three measures were created for analysis:

 Min Date: 6/12/2022 and Max Date: 4/2/2023 (I selected them in Date Slicer too)

Measures are-

Latest date = MAX(Data[Date])

Second date = CALCULATE(MAX(Data[Date]),CALCULATETABLE(LASTNONBLANK('Calendar'[Date],CALCULATE([Latest date])),DATESBETWEEN('Calendar'[Date],MINX(ALL('Calendar'),'Calendar'[Date]),[Latest date]-1)))

Status on latest date = CALCULATE(MIN(Data[Status]),DATESBETWEEN('Calendar'[Date],[Latest date],[Latest date]))

Status on second date = CALCULATE(MAX(Data[Status]),DATESBETWEEN('Calendar'[Date],[Second date],[Second date]))

Many thanks in advance.

[Anonymous · Accepted solution]

Hi @thinker_02 

Please try this:

I create a new table:

Then Create a measure:

CountID = 
	VAR _Newvalue = SELECTEDVALUE('Table 2'[Value])
	VAR _countID = SWITCH(
		_Newvalue,
		"Count1", "ID1",
		"Count2", "ID2"
	)
	RETURN
		CALCULATE(
			COUNTROWS('Data'),
			FILTER(
				ALLSELECTED('Data'),
				'Data'[Status] = "High" && 'Data'[Product ID] = _countID
			)
		)

The result is as follow:

^{Best Regards,}

^{Zhengdong Xu}

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

[Ashish_Mathur · Accepted solution]

Hi,

I had shared those measures in another post with you.  You should have ideally continued asking your question in the same thread.  Try this measure

Measure = countrows(Data[Product ID]),[Status on latest date]="High")

Hope this helps.

[thinker_02]

@Anonymous Can you attach the PBIX file pls? I tried to do same but didnt work.  I have multiple IDs in data such as ID1, ID2, ID3, ID4 etc. I must be missing something. Many thanks.

[Anonymous]

Hi @thinker_02 

Here is the pbix file:

^{Best Regards,}

^{Zhengdong Xu}

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

[Anonymous · Accepted solution]

Hi @thinker_02 

Please try this:

I create a new table:

Then Create a measure:

CountID = 
	VAR _Newvalue = SELECTEDVALUE('Table 2'[Value])
	VAR _countID = SWITCH(
		_Newvalue,
		"Count1", "ID1",
		"Count2", "ID2"
	)
	RETURN
		CALCULATE(
			COUNTROWS('Data'),
			FILTER(
				ALLSELECTED('Data'),
				'Data'[Status] = "High" && 'Data'[Product ID] = _countID
			)
		)

The result is as follow:

^{Best Regards,}

^{Zhengdong Xu}

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}