Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.