How to disregard slicer on measures

[PBI_newuser]

Hi,

I have a sample raw data as shown below. I wanna create a table as below with slicer "Product" and "Team".

How to create measure to show the count of Job# disregard the slicer selected if the type = Repair?

For example, if user selects Product = P-1, the Job# Count for Type = Repair should include the count of P-1 but for Type = Install, it should exclude the count of P-1.

Expected Output:

Type	Job# Count	Serial Number Count
Install
Repair

Sample Raw Data:

Type	Job#	Serial Number	Product	Team
Install	J-123	AA	P-1	TeamA
Repair	J-234	BB	P-2	TeamB
Install	J-345	CC	P-1	TeamA
Repair	J-456	DD	P-2	TeamB
Install	J-345	CC	P-1	TeamA
Repair	J-456	DD	P-2	TeamB

[HarishKM]

@PBI_newuser Hey Mate .
# of job = calculate(countrows(table name))
or
# of job = countrows(table name)
then drag that measure to matrix to get desire output.

[Jihwan_Kim]

Hi, @PBI_newuser 

Please check the below picture and the sample pbix file's link down below whether it is what you are looking for.

I used distinct count function in order to count Job No and Serial No. 

If you want to just count the occurrences, then you can use another DAX formula.

I created one table for the slicer, that is disconnected from the main table.

https://www.dropbox.com/s/9bc47lops9wgao2/pbinewuser.pbix?dl=0 

Job No Count =

IF (

ISFILTERED ( Products[Product] ),

COALESCE (

CALCULATE (

DISTINCTCOUNT ( Data[Job#] ),

Data[Product] IN ALLSELECTED ( Products[Product] )

),

""

),

""

)

Serial No Count =

VAR excepttable = EXCEPT( SUMMARIZE(ALL(Data), Data[Product]), VALUES(Products[Product]))

RETURN

IF (

ISFILTERED ( Products[Product] ),

COALESCE (

CALCULATE (

DISTINCTCOUNT ( Data[Serial Number] ),excepttable

),

""

),

DISTINCTCOUNT(Data[Serial Number])

)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

[PBI_newuser]

Hi @Jihwan_Kim , thanks for the solution.

But, i wanna count the Job# and Serial Number as below.

If Product = P-1 is selected, this is the expected output.

For Type=Install/Escalation, it should regard the selection of slicer and count all the job# and serial number.

Expected Output:

Type	Job# Count	Serial Number Count
Install	2	2
Repair	1	1
Escalation	2	2

Sample Raw Data:

Type Job# Serial Number Product Team
Install J-123 AA P-1 TeamA
Repair J-234 BB P-1 TeamB
Escalation J-678 DD P-1 TeamA
Install J-345 CC P-2 TeamA
Repair J-456 DD P-2 TeamB
Escalation J-111 CC P-2 TeamB

[Jihwan_Kim]

Hi, 

Thank you for your feedback, and sorry to misunderstand your question from the first time.

I fixed my measures and please check the link down below.

But, I still do not understand is that, for instance, if P-1 is selected, distinctcount serial number for install/escalation -> P-1 only? or P-1 and P-2 ?

My measure is counting only P-1.

If it is counting P-1 nd P-2, then please let me know

Thanks.

https://www.dropbox.com/s/9bc47lops9wgao2/pbinewuser.pbix?dl=0 

[PBI_newuser]

Hi, thanks for the file. 

If P-1 is selected, distinctcount serial number for install/escalation --> P-1 and P-2

If P-1 and TeamA are selected, disctinctcount serial number for install/escalation --> P-1 & P-2 and TeamA & TeamB

[Jihwan_Kim]

Hi, 

thank you for your feedback.

I can understand your first explanation.

It is quite difficult to understand your second explanation.

Based on the first explanation, I fixed the sample pbix file

Please check the link below.

https://www.dropbox.com/s/9bc47lops9wgao2/pbinewuser.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

[PBI_newuser]

Hi @Jihwan_Kim , sorry for the confusion.

If there is no selection for Product slicer, can the table shows the distinctcount for all products?

Let me explain the second case.

For instance, there are 2 slicers which are Product and Team.

If the user select P-1 and TeamA, disctinctcount serial number for install/escalation should be including all --> P-1 & P-2 and TeamA & TeamB

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below link.

https://www.dropbox.com/s/9bc47lops9wgao2/pbinewuser.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.