topic DAX expression using specific field in Report Server

DAX expression using specific field

Kavya123 — Thu, 28 Jan 2021 11:34:27 GMT

Hi Everyone,

Please help me to write expression in DAX.

My current expression is Sum( Value) . I want to display the value based on specific field Response Type.

For ex: 1.If Responsetype is ###,###,###,##0.0% then value would be 104,336.8%

2. If Responsetype is ###,###,###,##0 then Value is 10,972 (without percentage)

Response Type

###,###,###,##0

###,###,###,##0%

###,###,###,##0.0

###,###,###,##0.0%

###,###,###,##0.00

###,###,###,##0.00%

$###,###,###,##0

$###,###,###,##0.00

Thanks in advance

Re: DAX expression using specific field

FarhanAhmed — Thu, 28 Jan 2021 14:36:16 GMT

You may need to create CASES for each response type and try to use FORMAT DAX expression to convert your values in the specified format.

https://docs.microsoft.com/en-us/dax/format-function-dax

https://docs.microsoft.com/en-us/dax/switch-function-dax

Re: DAX expression using specific field

Kavya123 — Fri, 29 Jan 2021 14:04:16 GMT

Thanks for your response.

I want to format based on specific field Responsetype.

Re: DAX expression using specific field

josef78 — Sun, 31 Jan 2021 15:03:25 GMT

Hi,

maybe try another forum group, like DAX commands and tips, bacause this is related on onpremise report server deployment, but....

...if you need sum, it is measure, you must solve how aggregate multiple formats to single result ... you can try:

if(HASONEVALUE([Responsetype]),format(SUM([Value]),FIRSTNONBLANK([Responsetype],1)),blank())

it will format sum of coumun based on format string in another columns, and in case multiple formats (e.g. last total row in visualisation) is returned blank value. But maybe found better approach, I'm not DAX expert... 🙂

Re: DAX expression using specific field

Kavya123 — Mon, 01 Feb 2021 13:50:04 GMT

Thank you so much for your help.