DAX measure and wildcards

[bryanc78]

I have a column that looks like this

Score

Pass

Pass

Pass

Fail

Fail

Fail - Did not perform task

Fail - Did not assist customer

I want to group all the different Fails into one Fail bucket.  I can create these into groups but I'm not able to sort by the groups unfortunally and I want to be able to sort the Fails by highest to lowest in bar charts and matrix's

I have the Pass measure sorted:

Pass Measure = Calculate(CountA( 'Table1'[Agent]), 'Table1'[Score]="Pass")

But since I have different Fail values, I'm thinking a Widcard would work, to look for "Fail" so it captures all of them.

Thoughts?

[dax]

Hi @bryanc78 , 

I am not clear about your requirement,  if possible could you please explain "I can create these into groups but I'm not able to sort by the groups unfortunally and I want to be able to sort the Fails by highest to lowest in bar charts and matrix's" to me in details? Or you aslo could inform me more detailed information(such as your expected output and your sample data)? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

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

[Watsky]

Why not try not equal to Pass?

Pass Measure = Calculate(CountA( 'Table1'[Agent]), 'Table1'[Score]<>"Pass")

 Or how about looking at the first letter of the score to see if it starts with an f?

Pass Measure = Calculate(CountA( 'Table1'[Agent]), LEFT('Table1'[Score],1,"F"))

[bryanc78]

@Watsky 

Can't do the first option as I the column also contains "NA" and I need to ignore those.  I like the idea of the 2nd option but I'm getting a too many arguements were passed to the left function.

[Watsky]

@bryanc78  That's my bad I wrote it wrong. Try it this way.

Pass Measure = Calculate(CountA( 'Table1'[Agent]), LEFT('Table1'[Score],1) = "F"))

[Anonymous]

Fail Measure = COUNTX('Table'; FIND("Fail"; [Score]; 1; BLANK()))

[amitchandak]

create a new column like

new column = switch ( true(),

search("fail",table[col],1,0)>0 , "fail",

search("pass",table[col],1,0)>0 , "pass",

"Other"

)

And use this.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin