Power BI DAX formula for multiselect CRM field

[onurkrbck]

Hi All,

I don't have much experience at Power BI desktop and but need to complete below task.

I have a multi select option field in Dynamics CRM and this field is being used to calculate targets.

Field name : Target

Field Type: Multiselect option

Let's say options of this field: ABC, DEF, GHI

Salespersons can select all of them, or one or more of these options for a sales. I am trying to build a chart to show open opportunities' totals seperately with "ABC", "DEF", "GHI" target option selected in.

Since another option can be selected as well in the opportunity records, I could not make this calculation with my current knowledge.

	Revenue	Target Option
Record 1	$1000	ABC
Record 2	$2000	ABC; DEF
Record 3	$3000	ABC; DEF; GHI

I want DAX to give me the results of:

$6000 for Target ABC

$5000 for Target DEF

$3000 for Target GHI

My aim is to show them on a column chart.

Thanks for your responses in advance.

[onurkrbck]

Hi @amitchandak ,

Thanks for your reply. I tried, and it worked.

Maybe you can help me again at the next step.

Here is my extended table;

	Revenue	Target1	Target2	Target3
Record 1	$1000	ABC
Record 2	$2000	ABC	DEF
Record 3	$3000	ABC	DEF	GHI

I am trying to SUM of the Revenue values of the records which Target "DEF" has been choosen in.

For this example, calculation must give me $5000 result but I could not able to accomplish the formula.

Measure = CALCULATE(SUM('opportunities'[Revenue],IF('opportunities'[Target1]="DEF" || 'opportunities'[Target2]="DEF" || 'opportunities'[Target3]="DEF"))

Where do I make mistkae at?

Thanks

[amitchandak · Accepted solution]

@onurkrbck , Split the column by delimiters in power Query

https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/

Split Column Power Query: https://youtu.be/FyO9Vmhcfag