IF Statement that uses one column name or another based on a different column?

[Anonymous]

Hi,

I want to create something called Selected Segment that uses the following logic:

User selects a geography (US grocery retailers in this case)

If user selects any geography that starts with ABS then I want to following to happen, else any other geography does something different.

Data source 'dimproduct' has 2 columns[Segment] and [Segment 2]

Data source 'dimgeography' has the [Geography] column

Data source 'fct5tfupcmerge' has all the sales data I am wanting to report on.

I want to create a new measure called Selected Segment that will look at 'dimgeography'[Geography] and if the left 3 characters equal "ABS" then I want it to use [Segment] otherwise use [Segment 2].

Can this be done?

Geography 

[v-angzheng-msft]

Hi, @Anonymous 

Could you please consider sharing more details about it and posting expected result so it is clear on what needs to be implemented?  And It would be great if there is a sample file without any sesentive information here.

It makes it easier to give you a solution.

1. Sample (dummy dataset) data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Best Regards,
Community Support Team _ Zeon Zheng

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

[amitchandak]

@Anonymous , based on what I got a measure like

calculate(Countrows(Table), filter(dimproduct, if(left(selectedvalue(dimgeography[Geography]),3) = "ABS", dimproduct[Segment] in values(Segment[Segment] ) ,dimproduct[Segment2] in values(Segment[Segment] ))))

[Anonymous]

@amitchandak 

Thank you for your assistance.......here is the measure as I had to adjust it slightly from yours:

SEGMENT SELECTED = calculate(Countrows(fct5tfupcmerge), filter(dimproduct, if(left(selectedvalue(dimgeography[Geography]),3) = "ABS", dimproduct[Segment] in values(dimproduct[Segment] ) ,dimproduct[Segment (ABS)] in values (dimproduct[Segment] ))))

Now it won't let me update my original Segment slicer with this new Segment Selected field because it doesn't think it is text?

[amitchandak]

@Anonymous , Oh, Sorry I think got it wrong, seem like you want to switch the column in the slicer

You have to unpivot both columns, you will get a new column Segment name

Add a new column in

new column =

if(dimproduct[Segment name], "Segment", "ABS", "Non ABS")

measure  =

var _1= left(selectedvalue(dimgeography[Geography]),3) = "ABS"

return

countrows(filter(dimproduct, (_1 && dimproduct[new column] ="ABS") ||  (_1 =false() && dimproduct[new column] ="Non ABS")))

Check this measure is non blank

[Anonymous]

@amitchandak 

Did you see my last reply? I am not sure exactly what you are saying I need to do?

[Anonymous]

@amitchandak ,

I am not sure I am quite following you. First I need to unpivot what two columns in what table? 

Then, after unpivoting, I need to create this new column in what table?

[amitchandak]

@Anonymous ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.