Forum Discussion

azakir's avatar
azakir
Resolver I
4 years ago
Solved

IF Condition To Display All Values From A Column

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:

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

5 Replies

  • Kaly's avatar
    Kaly
    Resolver II
    4 years ago

    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's avatar
      azakir
      Resolver I
      4 years ago

      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. 

  • MahyarTF's avatar
    MahyarTF
    Memorable Member
    4 years ago

    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's avatar
      azakir
      Resolver I
      4 years ago

      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

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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