IF condition to show all values

[azakir]

Hi Guys.

Need some help on an IF formula that I am trying to work out. Here's sample data:

Equipment OEM               Equipment Code

320         EX234

793         EX235

950         EX236

793         EX237

950         EX238

950         EX239

What I am after is a measure which counts the number of Equipment code if OEM model is "793", else shows the equipment code.

I've worked out a basic formula which satisfy the first condition of count, but does not show all the equipment codes (possibly because of MAX):

*Equipmentdescorcount = IF(MAX('Equipment OEM Model'[Equipment OEM Model Code])="793", count(Equipment[Equipment Description]), MAX(Equipment[Equipment Description]))

Ideally this would show something like below:

Equipment OEM               Equipment Code

320         EX234

793         2

950         EX236

                EX238

                EX239

[ShahabHoghooghi · Accepted solution]

You can use this DAX code and get the result:

Equipment Code Replacement =

VAR _Numbers = CALCULATE(COUNT(Equipment[Equipment Code]),

                         Equipment[Equipment OEM]=793,

                         REMOVEFILTERS(Equipment[Equipment Code]))

RETURN

IF(SELECTEDVALUE(Equipment[Equipment OEM])=793,

                                _Numbers,

                                SELECTEDVALUE(Equipment[Equipment Code]))

[azakir]

Thanks @ShahabHoghooghi this worked

[YalanWu_test]

Hi, @azakir ;

You could create a column :

Column = IF('Table'[Equipment OEM]="793","1",[Equipment Code])

Then create a measure.

*Equipmentdescorcount = IF(MAX('Table'[Equipment OEM])="793", COUNT('Table'[Equipment Code]), MAX('Table'[Equipment Code]))

Add a matrix and setting it.

And 

The final show:

Best Regards,

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

[azakir]

Thanks @YalanWu_test This is great. 

[ddpl]

@azakir Try this,

Measure = CALCULATE(
                   if(SELECTEDVALUE('Table'[Equipment OEM]) = 793,
                       COUNT('Table'[ Equipment Code]),
                       MAX('Table'[ Equipment Code])))

[azakir]

Hi @ddpl 

Thanks for your reply. I did use this formula in the original post. The "MAX ('Table'[Equipment Code])) gives me only one value for 950. What I would love to have is to show all the 3 values for "950". Anything I could use instead of "MAX" to give me that solution?

[ddpl]

@azakir Create one calculated column as per below

Count and Code = if('Table'[Equipment OEM] = 793, CONVERT(COUNTROWS(FILTER('Table','Table'[Equipment OEM] = EARLIER('Table'[Equipment OEM]))), STRING),'Table'[Equipment Code])

Then table visual as per below 

Please accept as solution if its worked.