Error formula Switch

[Syndicate_Admin]

Good

I am trying to get a piece of data through a metric using the switch function and several variables. The problem I have is that depending on which week the measure works well or not.

I explain I am trying to put in a measure that depending on the sector and the market the objective to add is one column or another. The problem I have is that some of them put the data well while others do not. I attach the measure created and the result that comes out and the one that should come out.

I have already managed to solve the problem.

Objective by Market and Sector =

var Market = SELECTEDVALUE(Clients[Market])

var Sector = SELECTEDVALUE(Clients[Sector])

var concatenated = Market & " - " & Sector

return

SWITCH( concatenated,

"EXPORT - BATTERIES", sum(Objectives[Objective 2021 International Batteries]),

"EXPORT - HYGIENIC", sum(Objectives[Objective 2021 International Hygienic]),

"EXPORT - FOOD", sum(Objectives[Objective 2021 International Food]),

"EXPORT - ENVELOPES", sum(Objectives[Objective 2021 International Envelopes]),

"EXPORT - MISCELLANEOUS", sum(Objectives[Objective 2021 International Others]),

"EXPORT - ", sum(Goals[International Goal 2021]),

"NATIONAL - BATTERIES", sum(Objectives[Objective 2021 National Batteries]),

"NATIONAL - HYGIENIC", sum(Objectives[Objective 2021 National Hygienic]),

"NATIONAL - FOOD", sum(Objectives[Goal 2021 National Food]),

"NATIONAL - ENVELOPES", sum(Objectives[Objective 2021 National Envelopes]),

"NATIONAL - MISCELLANEOUS", sum(Objectives[Objective 2021 National Others]),

"NATIONAL - ", sum(Objectives[National Goal 2021]),

" - ", 0)

As you can see in the image, choosing the "Export" market and the "Hygienic" sector, the data that appear in "Objectives by market and Sector" Should be the same as "Objective 2021 international hygienic". What I do not understand well is why in some weeks the data is fine and yet in others it is not... I've been trying all afternoon to figure out why but I can't find a solution.

If you can help me I would appreciate it.

Thank you

PS: I have managed to fix the problem by taking the sector and the market to separate tables. It can be put as "solved"

[v-xiaoyan-msft]

Hi @Syndicate_Admin ,

You can provide your sample file after you have modified the sensitive information by the following ways, thank you.

How-to-provide-sample-data-in-the-Power-BI-Forum 

Hope it helps,

Community Support Team _ Caitlyn

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

[Syndicate_Admin]

Well, it seems that I have not managed to solve the problem.

As soon as I want to create a graph with the capture of sales of this year, last year and with respect to the objective (the measure that I have set) making a filter in the year I do not get the data to do it well. I can't attach the file as it's company data. I'm going to try to modify some data and upload the file.

Thank you

[v-xiaoyan-msft]

Hi @Syndicate_Admin ,

Does the replies above solve your problem? If it has been solved, please mark the correct reply as the standard answer to help the other members find it more quickly.Thank you very much for your kind cooperation!

Hope it helps,

Community Support Team _ Caitlyn

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

[AllisonKennedy]

@Syndicate_Admin  I'm not sure what your DAX Looks like for the measures you're referencing, but based on your solution it is likely related to the way you've designed the filters in the individual measures. Putting them in separate tables enables you to do more advanced filtering.

See if these blogs help: 

https://excelwithallison.blogspot.com/2021/09/advanced-filter-or-vs-and.html

https://www.sqlbi.com/articles/expanded-tables-in-dax/