Skip to main content
cancel
Showing results for 
Search instead 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

Reply
azakir
Resolver I
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
ShahabHoghooghi
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]))

ShahabHoghooghi_0-1660033187684.png

 

View solution in original post

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

ShahabHoghooghi_0-1660033187684.png

 

Thanks @ShahabHoghooghi this worked

YalanWu_test
Helper I
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.

YalanWu_test_0-1660029498871.pngYalanWu_test_1-1660029513558.pngYalanWu_test_2-1660029525464.pngYalanWu_test_3-1660029540039.png

YalanWu_test_5-1660029639273.png

 

And 

YalanWu_test_4-1660029619212.png

The final show:

YalanWu_test_8-1660029716677.png

Best Regards,

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

Thanks @YalanWu_test This is great. 

ddpl
Solution Sage
Solution Sage

@azakir Try this,

 

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

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?

@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 

 

ddpl_0-1659942953991.png

 

Please accept as solution if its worked.

Helpful resources

Announcements
Fabric Community Conference

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

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