topic Re: The format of the measure in DAX Commands and Tips

The format of the measure

Anonymous — Fri, 23 Oct 2020 06:38:22 GMT

Hi all.

How can bring the following outcome of my measure?

Basically it is var1 / var2 (var3). But not sure how to wrap it in brackets and separate with the slash.

Re: The format of the measure

amitchandak — Fri, 23 Oct 2020 07:21:44 GMT

Anonymous

assuming they are part of the return

var1 & " / " & var2 & "(" & var3 & ")"

order replace var1,2 and 3 with measures

Re: The format of the measure

themistoklis — Fri, 23 Oct 2020 07:35:22 GMT

Anonymous

Make sure you convert numbers to string. You can either do it in the variable or directly. See example below

var1 = CONVERT(Table[Amount],STRING)

var2 = CONVERT(Table[Amount2],STRING)

var3 = CONVERT(Table[Percent],STRING)

RETURN

var1 & " / " & var2 & " (" & var3 &")"

Or do it directly in a column. See below:

Column = CONVERT(Table[Amount],STRING) & " / " & CONVERT(Table[Amount2],STRING) & " (" & CONVERT(Table[Percent],STRING) &")"

Re: The format of the measure

Anonymous — Fri, 23 Oct 2020 09:30:53 GMT

Thanks, amitchandak

But getting an error: "can not convert value '/' of type Text to type true/false"

My measure:

Chat 2 =

VAR People_Count =

CALCULATE (

DISTINCTCOUNT ( 'Teams statistics'[User Principal Name] ) - 1,

'user AD'[IsManager] = 1

|| 'user AD'[IsManager] = 0)

VAR min_level =

MIN ( 'user AD'[User Org Level] )

VAR Yes_Count =

CALCULATE (

DISTINCTCOUNT ( 'Teams statistics'[User Principal Name] ),

'Teams statistics'[Chat] = "Yes",

'user AD'[IsManager] = 1

|| 'user AD'[IsManager] = 0,

'user AD'[User Org Level] <> min_level)

VAR No_Count =

CALCULATE (

DISTINCTCOUNT ( 'Teams statistics'[User Principal Name] ),

'Teams statistics'[Chat] = "No",

'user AD'[IsManager] = 1

|| 'user AD'[IsManager] = 0,

'user AD'[User Org Level] <> min_level

)

VAR Percentage = IF (

Yes_Count > 0,

DIVIDE ( Yes_Count, People_Count ), 0)

RETURN

CONVERT(Yes_Count, STRING) && "/" && CONVERT(People_Count, STRING) && "("&& CONVERT(Percentage, STRING) &&")"

Re: The format of the measure

Anonymous — Fri, 23 Oct 2020 09:31:59 GMT

themistoklis , thanks

But getting an error: "can not convert value '/' of type Text to type true/false"

my measure is in previous comment.

Re: The format of the measure

themistoklis — Fri, 23 Oct 2020 09:38:40 GMT

Anonymous

remove double ampersand (&&) and put just one (&)

Replace this

CONVERT(Yes_Count, STRING) && "/" && CONVERT(People_Count, STRING) && "("&& CONVERT(Percentage, STRING) &&")"

With this

CONVERT(Yes_Count, STRING) & "/" && CONVERT(People_Count, STRING) & "("&& CONVERT(Percentage, STRING) &")"

Re: The format of the measure

Anonymous — Fri, 23 Oct 2020 10:31:15 GMT

wow, themistoklis , thank you! Will have to change my habbits 🙂

Would you also happen to know how to format the following variable so it would bring percentage with no decimal places ?

VAR Percentage = IF (

Yes_Count > 0,

DIVIDE ( Yes_Count, People_Count ), 0)

Re: The format of the measure

themistoklis — Fri, 23 Oct 2020 10:41:37 GMT

Anonymous

Select the Measure from fields section.

Then on Menu on top --> Column Tools -->Format section (put 0)

Re: The format of the measure

Anonymous — Fri, 23 Oct 2020 10:46:01 GMT

themistoklis

Nevermind, figured out the formatting part myself 🙂 thanks for help!

VAR Percentage = FORMAT(IF (

Yes_Count > 0,

DIVIDE ( Yes_Count, People_Count ), 0), "0%")