cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Resolver I

## IF condition to show all values

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

1 ACCEPTED SOLUTION
Frequent Visitor

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]))

7 REPLIES 7
Frequent Visitor

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]))

Resolver I

Thanks @ShahabHoghooghi this worked

Helper I

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.

Resolver I

Thanks @YalanWu_test This is great.

Solution Sage

@azakir Try this,

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

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?

Solution Sage

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

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors