Mixing something like COUNTIFS with the min value for each case.

[jmateu88]

Hello,

I am trying to find the way to do in Power BI what I would do in Excel using COUNTIFS (for the first part of the message)
After this, a conditional with the minimum value (for the second part).

The purpose is to know which machines are being repaired and if not, when is the next maintenance (depending on the ranking).

For that, the first I consider (I may be wrong) is to get a DAX formula (similar to Excel COUNTIFS) to get if a machine is being repaired or not.

In excel would be something like this;

We can see in the 'column I' for each registers de amount of tasks being done for each machine.

Once we have this, I want to obtain another column (the good one)(in the example 'K'), where all machines in process are in 'Yes' and those machines which done ('C' in the example), only is shown the next one (the one with the minimum value in column 'H') too.

Explaining this in the opposite way, I do not want to see any of the machines in status 'Pending' unless that register has the minimum value in column 'H' and that machine has not any activity in process.

Finally, thank you for your grateful help! This might be easy if you have the clue but for me, it is getting too complicated.
Regards!

[jmateu88]

Thank you @Greg_Deckler . I already gave you a like in your other post. Very interesting the comparison.

However, I still have doubts about the way you use de filterexpression. I suppose that in filterexpression is where others machines (in the example) are filtered depending on the row?

Thanks in advance! I already tryied with FILTER but i did not find the way to use properly the filterexpression.

[Greg_Deckler]

@jmateu88 Can you post your sample data as text and I can create a specific solution for it. Otherwise I'm likely to miss a paren or not account for a particular edge case. But, could be:

Column =
  VAR __Machine = [MachineID]
  VAR __Seq = MINX(FILTER('Table',[MachineID] = __Machine),[Sequential Number])
RETURN
  IF([Status] = "In process" || [Sequential Number] = __Seq,"Yes","No")

[jmateu88]

Hello @Greg_Deckler ,

Here the data;

Machine	Status	Seq.no
A	In process	1
B	In process	1
C	Pending	1
A	In process	2
B	Pending	2
B	Pending	3
C	Pending	2
A	Pending	3
B	Pending	4
C	Pending	3
A	Pending	4
B	Pending	5
A	Pending	5

Here trying in Power BI.

Thank you!!

[Greg_Deckler · Accepted solution]

@jmateu88 PBIX is attached below signature. This is the column I created:

Column = 
  VAR __Machine = [Machine]
  VAR __Seq = MINX(FILTER('Table',[Machine] = __Machine),[Seq.no])
RETURN
  IF([Status] = "In process" || [Seq.no] = __Seq,1,0)

[jmateu88]

Woow @Greg_Deckler . That was amazing.

I learnt a lot with this simple and incredible expression. Thank you very much!

The solution is more than valid!

[Greg_Deckler]

@jmateu88 COUNTIF equivalent is COUNTX(FILTER(...),...) CO-CU Excel to DAX Translation - Microsoft Power BI Community