Calculation based on customized filter

[mogunase1]

Hi,

I have dataset like,

VIN NO	Category	Status
ABC1	Axile	PASS
ABC1	Cabin	PASS
ABC1	Engine	FAIL
DEF2	Axile	FAIL
DEF2	Cabin	PASS
DEF2	Engine	FAIL
GHI3	Axile	PASS
GHI3	Cabin	PASS
GHI3	Engine	PASS

I want Calculation based on customized filter,

1. Distinct count of VIN where Status we need to consider only "PASS", For same VIN number we have Pass and Fail status, then we need to condider under Fail category.

For the Above the answer needs to be "PASS COUNT = 1" (I.E., FOR GHI3).

2. Top 2 defect category name ,Name of category with filter Fail.

Answer needs to be "Top 2 Defect Cat = Engine , Axile"

Please help me to create a DAX for this.

Thanks,

Mohanraj

@amitchandak @Jihwan_Kim 

[Jihwan_Kim]

Hi,

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

All measures are in the sample pbix file.

Category Measure =

SWITCH (

SELECTEDVALUE ( Slicer[Status] ),

"FAIL",

IF (

SELECTEDVALUE ( Slicer[Status] ) IN VALUES ( Data[Status] ),

"FAIL",

BLANK ()

),

"PASS",

IF (

SELECTEDVALUE ( Slicer[Status] )

IN VALUES ( Data[Status] )

&& COUNTROWS ( VALUES ( Data[Status] ) ) = 1,

"PASS",

BLANK ()

),

"select status from slicer"

)

VIN # Pass count =

SUMX (

VALUES ( Data[VIN NO] ),

CALCULATE ( IF ( "FAIL" IN VALUES ( Data[Status] ), 0, 1 ) )

)

https://www.dropbox.com/s/emhxbju3apwsm4m/mogunasev2.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.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[mogunase1]

Hi Jihwan,

For the First Question i got answer from you (Pass Count)

But for 2nd Question,

My Actual requirement is 

2. Top 2 defect category name with respect to count of Defect category ,Filter is Name of category with filter Fail needs to apply in DAX.

Example for above scenario - 

Top  2 Defect is,

Axile

Engine

Thanks,

Mohan

[Jihwan_Kim · Accepted solution]

Hi, @mogunase1 

Thank you for your feedback.

Please check the below picture and the link down below.

Top2 defect category =

CONCATENATEX (

TOPN ( 2, VALUES ( Data[Category] ), [Count Fail], DESC ),

Data[Category],

", ",

[Count Fail], DESC

)

https://www.dropbox.com/s/emhxbju3apwsm4m/mogunasev2.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.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[mogunase1]

Hi,

Thanks a lot.

Now working fine.

Thanks,

Mohanraj

[Ashish_Mathur]

Hi,

Use these measures

Failed count = CALCULATE(COUNTROWS(Data),Data[Status]="Fail")

Measure = CONCATENATEX(TOPN(2,VALUES(Data[Category]),[Failed count] ),Data[Category],", ",[Failed count],DESC)

Hope this helps.