Forum Discussion

James__'s avatar
James__
Helper I
4 years ago
Solved

Calculated column HELP!

Hi All,

 

I'm trying to create a calculated column based on some specific criteria. 

 

So i have a list of LCV registration numbers and i want to identify which ones still have an mot. So the column should be: vehicles[vehicle_has_MOT). I want to see "1" when the vehicle has the MOT product, and "0" when it doesn't.

 

MOT is a parent product in the same table, and has the three sub products in a separate column:

 

Reg        Product        Product Type        Policy Count (we use 1 or 0)  CALC_COLUMN

ABC        MOT            Single Payment     1

ABC        MOT            Monthly Payment  0

ABC        MOT            FOC                       0

 

I want the CALC_COLUMN to do something like vehicles_[has_MOT]=1

 

apologies if this is obvious, i'm new to Power BI 🙂

Need Help
  • PijushRoy's avatar
    PijushRoy
    4 years ago

    Hi James__ 

    Can you please try this DAX for calculated column

    CAL_COLUMN =
    SWITCH(
    TRUE(),
    'Table (2)'[Product]="MOT" && 'Table (2)'[Product Type]="Single Payment", 1,
    0)
     
    If solve your requirement, please mark this answer as SOLUTION
    If this comment helps you, please LIKE this comment/Kudos

6 Replies

  • PabloDeheza's avatar
    PabloDeheza
    Solution Sage
    4 years ago

    Hi there!

    You can try this:

    IF( Product = "MOT", 1, 0 )

    Let me know if that helps!

  • PijushRoy's avatar
    PijushRoy
    Community Champion
    4 years ago

    James__ 

    In your example, 

    Reg        Product        Product Type        Policy Count (we use 1 or 0)  CALC_COLUMN

    ABC        MOT            Single Payment     1

    ABC        MOT            Monthly Payment  0

    ABC        MOT            FOC                       0

     

    2nd and 3rd line has product MOT, but why you are showing 0 inplace of 1
    Please confirm

    • James__'s avatar
      James__
      Helper I
      4 years ago

      Hello PijushRoy 

       

      The MOT has 3 separate products: Single payment, monthly payment and FOC. In the example above, this particular van has a single payment MOT product, and not monthly payment or FOC.

       

      Hope that helps.

      J

      • PijushRoy's avatar
        PijushRoy
        Community Champion
        4 years ago

        Hi James__ 

        Can you please try this DAX for calculated column

        CAL_COLUMN =
        SWITCH(
        TRUE(),
        'Table (2)'[Product]="MOT" && 'Table (2)'[Product Type]="Single Payment", 1,
        0)
         
        If solve your requirement, please mark this answer as SOLUTION
        If this comment helps you, please LIKE this comment/Kudos
  • bilalrana's avatar
    bilalrana
    Resolver I
    4 years ago
    CALC_COLUMN = IF( Product = "MOT", 1, 0 )

    Paste it in the new column.

    Accept it as a solution if its help you to solve your problem.