Switch true does not keep the format of my measure

[DM_BI]

Hello,

I am using a a SelectMeasure so that the user can filter by the indicator he wants (facturation or sales). But by using switch true I lose the format of [sales] (which should appear as an entire number). Both facturation and sales appear with "€" at the end. Here is my formula :

Indicateur_gestión = IF(ISCROSSFILTERED(SelectMeasure[Measure_gestión]);

SWITCH(TRUE();

VALUES(SelectMeasure[Measure_gestión])="Facturación";[Fact YTD_gestión];

VALUES(SelectMeasure[Measure_gestión])="Sales";[Sales YTD_Gestión];BLANK());BLANK())

How to tell DAX that I want the facturation to be in euros and the sales to be entire numbers? Is it possible? I tried with FORMAT inside VALUES or outside VALUES but it does not work...

Thank you,

[v-yulgu-msft]

Hi @DM_BI,

Please modify the measure formula as:

Indicateur =
IF (
    ISCROSSFILTERED ( SelectMeasure[Measure] ),
    SWITCH (
        TRUE (),
        VALUES ( SelectMeasure[Measure] ) = "Facturation", FORMAT ( [Sum_fact], "Currency" ),
        VALUES ( SelectMeasure[Measure] ) = "Number of sales", FORMAT ( [Sum_number of sales], "General Number" ),
        BLANK ()
    ),
    BLANK ()
)

However, the data type of returned result by this measure will be converted to Text.

Best regards,

Yuliana Gu

[DM_BI]

Hi Yuliana,

Thank you, it works in the sample! My card show now the currency or general number. But it's weird that in my graph now I don't see any value... And in the sample it is impossible to drop the measure "Indicateur" to the value field of the chart. 

Here is the link 

Thank you for your help,

DM

[v-yulgu-msft · Accepted solution]

Hi @DM_BI ,

As I mentioned above, using FORMAT function will force the returned data type by measure to be converted to Text. It is not supported to add Text value into "Value" section of a chart.

In your scenario, to achieve such a conditional format in Chart visual, it is not available now.

Regards,

Yuliana Gu

[DM_BI]

Hello Yuliana,

Ok I got it thank you. Is there any idea to vote it ?

Best regards,

DM

[DM_BI]

You can see the problem in this pbix file : https://db.tt/tUBU6kkquf

When you select "facturation", the card on the left does not show that it is actually in euros.

Thank you in advance for anyone who can help me fix this...

DM