Measure does not work with "string" values

[Orstenpowers]

Dear all,

I have a problem with the measure below. I know that it does work when I want to return numeric values. I had found this great switching metrics in the web.

However, this time the measure should return values that are of type "string" and as I already understood, for this purpose using "SUM" does not work.

With my dashboard, I would like to select from a slicer OPTION 01 or OPTION 02 or OPTION 03 or.......

The measure below then should be integrated into a table or matrix visual and return all applicable string values.

Please refer to the screenshot attached. From the slicer at the top I would like to select one option and in return, the second visual should return all applicable string values. (in this case the available languages of documentation)..

Is there any chance to adjust the measure that it will work???

Measure Selected =

IF(

    HASONEVALUE('Measure Table'[Measure]),

        SWITCH(

            VALUES('Measure Table'[Measure]),

                "Option 01",SUM(Base_Data[PCF_Option_01]),

                "Option 02",SUM(Base_Data[PCF_Option_02]),

                "Option 03",SUM(Base_Data[PCF_Option_03]),

                SUM(Base_Data[- Net Amount])))

I am confident that someone here can help me. Hopefully, as I am lost. 😞

[Anonymous]

Hi @Orstenpowers ,

Try changing the sum part of the expression to:

SUMX('Base_Data',VALUE('Base_Data'[PCF_Option_01]))

This way it will be output as a numeric value

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[Orstenpowers]

Hi @Anonymous ,

Thanks for your valuable feedback!
I changed my measure accordingly, but unfortunately I get the following error message:

It states that type "Text" cannot be converted into type "Number".

Do you have any idea what else I could do?

[Anonymous]

Hi @Orstenpowers ,

I can't think of any other way, can you make the columns number by power query?

Best regards,
Community Support Team_ Scott Chang

[Orstenpowers]

Hi @Anonymous ,

Thank you for your continued help!
In Power Query, I changed from type "Text" to "Integer", but then the values change to "Error"...😕

[Anonymous]

Hi @Orstenpowers ,

Whether the column data has decimals, try to convert to Decimal number.

Best regards,
Community Support Team_ Scott Chang

[Orstenpowers]

Hi @Anonymous ,

Unfortunately this does not work. 😔

I think I do need a mearure that is able to return the different string values. 🤔 Do you or anybody else have an idea how to deal with this?

[Anonymous]

Hi @Orstenpowers ,

Can you share some of the sample data, I noticed you have commas in your numbers, it doesn't seem to be in text format.

Best regards,
Community Support Team_ Scott Chang

[Orstenpowers]

Hi @Anonymous ,

I prepared some sample data, please see below. I am very grateful that you spend time to help me. 👍

Hopefully this will help!?

https://www.dropbox.com/scl/fi/lko5by57cgo2j2pcdknfi/Sample_data_PBI.xlsx?rlkey=se9b5crvr9ljmk65lecrrluj0&st=pllu3z9u&dl=0

[Anonymous]

Hi @Orstenpowers ,

I feel very sorry that I don't know how this sample data will be used.

Best regards,
Community Support Team_ Scott Chang

[Orstenpowers]

Hi @Anonymous ,

I am not sure how to understand your reply. Is something missing to provide a potential solution? Or don't you know how to solve my problem? 

Looking forward to your reply I remain

[Anonymous]

Hi @Orstenpowers ,

That seems like a big gap from your original screenshot, and I don't quite understand your intent to sum it up. Based on the sample data you provided, what do you expect the expected results to be?

Best regards,
Community Support Team_ Scott Chang

[Orstenpowers]

Hi @v-tianyich-msft ,

On https://www.phdata.io/blog/switching-metrics-in-power-bi/  I had found a blog that explained the setup of a switching metrics.

This was quite interesting and I tried…and it worked. From now I had – on the one hand – a nice slicer that allowed me to select between EUR currency, local currency and order quantity and -on the other hand – another visual that automatically changed from EUR to local currency or quantity. So far, so good!

Now it is my intention to adjust this switching approach.

The slicer that is based on a table that I had to create manually, should allow me to select specific options that our instruments can be built off.

Our instruments consists of a model code that can comprise of up to 32 different features and for each of these features you can select one option.

Let’s say the model code is VR1C9K7HD4100LTRXA131WA0…and I would like to know details about feature 10 (10th position of the model code).

From my slicer I would like to select e.g. “Option 10” and another visual (table or matrix) should list all available options as I had tried to explain with my original posting.

So the measure that I am looking for should return rather text than figures, as my sample data should show.

Sorry, if my explanation did confuse you…

[Orstenpowers · Accepted solution]

Hi @Anonymous ,

On https://www.phdata.io/blog/switching-metrics-in-power-bi/  I had found a blog that explained the setup of a switching metrics.

This was quite interesting and I tried…and it worked. From now I had – on the one hand – a nice slicer that allowed me to select between EUR currency, local currency and order quantity and -on the other hand – another visual that automatically changed from EUR to local currency or quantity. So far, so good!

Now it is my intention to adjust this switching approach.

The slicer that is based on a table that I had to create manually, should allow me to select specific options that our instruments can be built off.

Our instruments consists of a model code that can comprise of up to 32 different features and for each of these features you can select one option.

Let’s say the model code is VR1C9K7HD4100LTRXA131WA0…and I would like to know details about feature 10 (10th position of the model code).

From my slicer I would like to select e.g. “Option 10” and another visual (table or matrix) should list all available options as I had tried to explain with my original posting.

So the measure that I am looking for should return rather text than figures, as my sample data should show.

Sorry, if my explanation did confuse you…