Forum Discussion

thinker_02's avatar
thinker_02
Regular Visitor
2 years ago
Solved

Count column, filtered by measure

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.

 



Data Protection
General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
    Ashish_Mathur
    2 years ago

    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.

4 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.

  • thinker_02's avatar
    thinker_02
    Regular Visitor
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

      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.