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...
  • 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.

     

JamesFR06's avatar
JamesFR06
Resolver 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.