How to count serial number regardless of slicer for certain type

[PBI_newuser]

Hi, how to create 2 measures with the below criteria. Here is the sample.

• If type = Escalation or type = Install is selected, show all the Count of Job# and Count of Serial Number regardless of the slicer [Product] and [Team] selected.

Example: Product=P-1 is selected, the expected result is as below.

Type	Serial No Count	Job Count
Escalation	2	2
Install	2	2
Repair	1	1

[amitchandak]

@PBI_newuser , you can use removefilters or all for product and Team

or

allexcept(Table, Table[Type], Table[Serial No])

example

calculate(count(Table[Job#]), filter(Table, [Escalation]= "Install"), all(Table[product]), all(Table[team]))

refer examples here

https://www.linkedin.com/pulse/five-recent-power-bi-functions-you-should-use-more-often-amit-chandak

https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/

https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept...

[PBI_newuser]

Hi @amitchandak , how to combine below 3 if statement in measure to get the output?

• If Type="Install", then count Job# with All (Data[Product]), All (Data[Team])
• If Type="Escalation", then count Job# with All (Data[Product]), All (Data[Team])
• If Type="Repair", then count Job#

[amitchandak · Accepted solution]

@PBI_newuser , Try measure like

Switch(true()
max(Table[Type])="Install", calculate(count(Table[Job#]), All (Data[Product]), All (Data[Team])),
max(Table[Type])="Escalation", calculate(count(Table[Job#]), All (Data[Product]), All (Data[Team])),
max(Table[Type])="Repair", count(Table[Job#])
)

or

sumx(values(Table[Type]),
Switch(true()
max(Table[Type])="Install", calculate(count(Table[Job#]), All (Data[Product]), All (Data[Team])),
max(Table[Type])="Escalation", calculate(count(Table[Job#]), All (Data[Product]), All (Data[Team])),
max(Table[Type])="Repair", count(Table[Job#])
)
)