Forum Discussion

MSargeant's avatar
MSargeant
Frequent Visitor
2 years ago
Solved

DAX Help Needed: Asset Numbers that Appear Only Once Under Multiple Filter Conditions

Hi All,

I need some help with formulating DAX for use with a stacked column graph by RATING column. The criteria with conditions are as follow.

The count for the number of assets when the following condtions are met:

1. The WORK DESCRIPTION must  =  'Examination' or 'Examination - Revisit'

2. COMPLETED? must = NO

3. STATUS must = COMPLETE

4. If  ASSET NUMBER has a WORK DESCRIPTION 'Examination', COMPLETED? = NO and also has a WORK DESCRIPITION 'Examination - Revisit' with COMPLETED? = YES, it shound be excluded from the count. 

 

Am looking for results like this:

 

 

Below is a data sample:

 

ASSET NUMBERSTATUSWORK ORDERWORK DESCRIPTION FINISH DATESEASONRATINGCOMPLETED?
TT-45009COMPLETE341966Examination10/10/20232023/2024ANO
TT-45033COMPLETE342039Examination10/10/20232023/2024CNO
TT-45033COMPLETE421252Examination - Revisit18/02/20242023/2024CNO
TT-45066COMPLETE331060Examination10/10/20232022/2023CNO
TT-45066COMPLETE421239Examination - Revisit18/02/20242023/2024CYES
TT-45083COMPLETE339234Examination10/10/20232023/2024CNO
TT-45083DESPATCHED422362Examination - Revisit 2023/2024C 
TT-45090COMPLETE342042Examination10/10/20232023/2024CNO
TT-45090COMPLETE419776Examination - Revisit18/02/20242023/2024CYES
TT-45121COMPLETE334760Examination10/10/20232022/2023BNO
TT-45121DESPATCHED423643Examination - Revisit 2023/2024B 
TT-45134COMPLETE330935Examination10/10/20232022/2023BNO
TT-45134DESPATCHED421403Examination - Revisit 2023/2024B 
TT-45185COMPLETE345518Examination10/10/20232023/2024CNO
TT-45185DESPATCHED422354Examination - Revisit 2023/2024C 
TT-45226COMPLETE334685Examination10/10/20232022/2023CNO
TT-45226DESPATCHED423653Examination - Revisit 2023/2024C 
TT-45279COMPLETE334695Examination10/10/20232022/2023BNO
TT-45349COMPLETE342384Examination10/10/20232023/2024CNO
TT-45349COMPLETE419958Examination - Revisit18/02/20242023/2024CYES
TT-45353COMPLETE342387Examination10/10/20232023/2024ANO
TT-45353COMPLETE419768Examination18/02/20242023/2024AYES
TT-45359COMPLETE334691Examination10/10/20232022/2023CNO
TT-45374COMPLETE334406Examination25/11/20232023/2024CYES
TT-45374PLANNING413089Evaluation 2023/2024C 
TT-45376COMPLETE334403Examination25/11/20232023/2024CYES
TT-45376PLANNING413056Evaluation 2023/2024C 
TT-45422COMPLETE334409Examination25/11/20232023/2024CYES
TT-45422PLANNING413091Evaluation 2023/2024C 
TT-45424COMPLETE345536Examination25/11/20232024/2025BYES
TT-45426COMPLETE334412Examination25/11/20232023/2024CNO
TT-45435RDESPATCH345528Examination 2023/2024B 
TT-45439COMPLETE333584Examination04/11/20232023/2024DYES
TT-45442COMPLETE340408Examination04/11/20232024/2025DNO
TT-45465COMPLETE333591Examination04/11/20232023/2024CYES
TT-45483COMPLETE333594Examination12/11/20232023/2024CNO
TT-45485COMPLETE333567Examination12/11/20232023/2024CYES
TT-45513PLANNING413008Evaluation 2023/2024C 
TT-45516COMPLETE346754Examination12/11/20232024/2025BYES
TT-45647PLANNING342550Examination 2023/2024A 
TT-45863COMPLETE318445Examination19/11/20232023/2024CNO

 

 

Any assistance would be appreciated.

 

Thanks

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi MSargeant ,

    JamesFR06  said it very well, I have another method here, I hope it will be helpful to you.

     

    1. Create a calculation table and filter out that WORK DESCRIPTION is Examination - Revisit, and COMPLETED? is YES.

    FilteredAssets =
FILTER(
SUMMARIZE(
'Table',
'Table'[ASSET NUMBER],
"WorkDesc", MAX('Table'[WORK DESCRIPTION]),
"Completed", MAX('Table'[COMPLETED?])
),
[WorkDesc] = "Examination - Revisit" && [Completed] = "YES"
)

    2. Create a measure to count the fields of the compound condition.

    AssetCount =
CALCULATE(
DISTINCTCOUNT('Table'[ASSET NUMBER]),
FILTER(
ALL('Table'),
'Table'[SEASON] = MAX('Table'[SEASON])
&& 'Table'[RATING] = MAX('Table'[RATING])
&& 'Table'[WORK DESCRIPTION] IN {"Examination", "Examination - Revisit"}
&& 'Table'[COMPLETED?] = "NO"
&& 'Table'[STATUS] = "COMPLETE"
&& NOT(
'Table'[ASSET NUMBER] IN VALUES(FilteredAssets[ASSET NUMBER])
)
)
)

    If your Current Period does not refer to this, please clarify in a follow-up reply.

     

    Best Regards,

    Clara Gong

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi MSargeant ,

    JamesFR06  said it very well, I have another method here, I hope it will be helpful to you.

     

    1. Create a calculation table and filter out that WORK DESCRIPTION is Examination - Revisit, and COMPLETED? is YES.

    FilteredAssets =
FILTER(
SUMMARIZE(
'Table',
'Table'[ASSET NUMBER],
"WorkDesc", MAX('Table'[WORK DESCRIPTION]),
"Completed", MAX('Table'[COMPLETED?])
),
[WorkDesc] = "Examination - Revisit" && [Completed] = "YES"
)

    2. Create a measure to count the fields of the compound condition.

    AssetCount =
CALCULATE(
DISTINCTCOUNT('Table'[ASSET NUMBER]),
FILTER(
ALL('Table'),
'Table'[SEASON] = MAX('Table'[SEASON])
&& 'Table'[RATING] = MAX('Table'[RATING])
&& 'Table'[WORK DESCRIPTION] IN {"Examination", "Examination - Revisit"}
&& 'Table'[COMPLETED?] = "NO"
&& 'Table'[STATUS] = "COMPLETE"
&& NOT(
'Table'[ASSET NUMBER] IN VALUES(FilteredAssets[ASSET NUMBER])
)
)
)

    If your Current Period does not refer to this, please clarify in a follow-up reply.

     

    Best Regards,

    Clara Gong

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • MSargeant's avatar
      MSargeant
      Frequent Visitor
      2 years ago

      Hi Anonymous,

       

       This solution works well for my needs.

       

      Thank you for your assistance!

       

      Kind regards,

      Michelle Sargeant

  • JamesFR06's avatar
    JamesFR06
    Icon for Resolver IV rankResolver IV
    2 years ago

    HI

     

    I have tried this. Tell me if it is ok for you. I created a new column in your table.

    Graph =
    if(Assets[STATUS]<>"Complete","No",
        if(and(Assets[STATUS]="COMPLETE",and(Assets[WORK DESCRIPTION]="Examination - Revisit",Assets[COMPLETED]="YES")),"No",
           
            var nbrows=calculate(COUNTROWS(filter(Assets,Assets[STATUS]="complete"&&Assets[WORK DESCRIPTION]="Examination - Revisit"&&Assets[COMPLETED]="YES")),ALLEXCEPT(Assets,Assets[ASSET NUMBER]))
            return
            if(Assets[STATUS]="complete"&&Assets[WORK DESCRIPTION]="Examination - Revisit"&&Assets[COMPLETED]="NO"&&nbrows>0,"Non","Yes")
        )
        )
     

     

    • MSargeant's avatar
      MSargeant
      Frequent Visitor
      2 years ago

      Hi, thanks but this does not quite work as the items I have hilighted below inside the blue circle should be 'No' for those Asset Numbers because COMPLETED? = YES for for Examination - Revisit  cancels the Examinations where COMPLETED? = NO

      Also, the Examinations inside the orange circle where COMPLETED = YES should be 'No 'as the original examination was successfully completed and should not be counted for the graph.