IF Condition To Display All Values From A Column

[azakir]

Hi Guys.

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

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

Ideally, the formula should show something like below:

[Kaly]

Hi @azakir ,

According to your description, you can get it by a calculated column instead of a measure, because before you put the measure in the visual, it only has three rows in the Equipment OEM column(320, 493, 950), then according to the context, the measure can only produce three rows.

There're two tables based on your formula, I create a sample.

Equipment OEM Model table:

Equipment table:

The two tables are related with the Equipment OEM column. Here's my solution, create a calculated column in the Equipment table:

Column =
IF (
    'Equipment'[Equipment OEM] = 793,
    CONVERT (
        COUNTROWS ( FILTER ( 'Equipment', 'Equipment'[Equipment OEM] = 793 ) ),
        STRING
    ),
    'Equipment'[Equipment Description]
)

Get the result:

Best Regards,

Kaly

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

[azakir]

Hi @Kaly 

Thank you for your reply and detailed explanation. You are right, having a measure won't really help. And your solution did the trick for me. Thank you for that. 

[Anonymous · Accepted solution]

Guys

My approach using a calculated table. However getting error for "variant data type". Any suggestions?

Table =
ADDCOLUMNS (
    SUMMARIZECOLUMNS (
        'Equipment OEM'[Equipment OEM],
        "Count of Equipment", COUNTROWS ( 'Equipment OEM' )
    ),
    "Measure",
        IF (
            [Count of Equipment] >= 2,
            [Count of Equipment],
            FORMAT ( 'Equipment OEM'[Equipment OEM], "#.##" )
        )
)

 Thanks

[MahyarTF]

Hi,

Not sure if it is the best solution :

- In Power Query, I create two duplicated tables from my specific Table (T1 and T2)

- In T1, I filter the 'Equipment OEM' = 732

- In T2, I filter the 'Equipment OEM' <> 732

- In T1, Group Rows based on the 'Equipment OEM'

- In T1, changed the type 'Equipment' type to Text

- Append the T1 and T2 and Apply them to Power BI

- In Power BI, create the Table visual and bring the 'Equipment OEM' and 'Equipment' column

[azakir]

Hi @MahyarTF . thanks for the suggestion. The issue is I've got a few more OEM model numbers that needs to be counted apart from 793. So thought I'll just include them in a formula for easy filtering