cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
James__
Frequent Visitor

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 🙂

1 ACCEPTED SOLUTION

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)
001.JPG
 
If solve your requirement, please mark this answer as SOLUTION
If this comment helps you, please LIKE this comment/Kudos

View solution in original post

6 REPLIES 6
bilalrana
Resolver I
Resolver I

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.

PijushRoy
Super User
Super User

@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

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

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)
001.JPG
 
If solve your requirement, please mark this answer as SOLUTION
If this comment helps you, please LIKE this comment/Kudos
James__
Frequent Visitor

Sorry, that didn't work 🤔

PabloDeheza
Super User
Super User

Hi there!

You can try this:

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

Let me know if that helps!

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Dev Camp Session 30

Ted's Dev Camp - January 26, 2023

This session will provide guidance and teach campers the skills required to build Power BI reports that support multiple languages.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.