Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Calculation based on customized filter

Hi,

 

I have dataset like,

VIN NOCategoryStatus
ABC1AxilePASS
ABC1CabinPASS
ABC1EngineFAIL
DEF2AxileFAIL
DEF2CabinPASS
DEF2EngineFAIL
GHI3AxilePASS
GHI3CabinPASS
GHI3Engine

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 

General Comment
Need Help
Tutorial Requests
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    Hi, Anonymous 

    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
    )
     
     

    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

     

     

5 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    5 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        Jihwan_Kim
        Super User
        5 years ago

        Hi, Anonymous 

        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
        )
         
         

        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