Format function in DAX bring the different customer data

Question

I am facing very weird situation while using the DAX format function. If I remove the format function it is working, If I add the format function it returns all the customer record even after applying the page level filter for specific customer.

When I comment FORMAT(hourNum,"#00")&":"&FORMAT(minuteNum,"#00") and Uncomment minuteNum . All the things work well.

I have also tried to replace format with concatenate function and it is still the same.

Can anyone please suggest what needs to be done here.

Output of the code is - 01:20

DAX Query

AverageX6IntervalLabel** = 
VAR hourNum=INT(divide([Average],60))
VAR minuteNum=MOD([Average],60)

RETURN
--minuteNum 
FORMAT(hourNum,"#00")&":"&FORMAT(minuteNum,"#00")

Added the picture of the actual DAX which is causing the issue, If I comment line 10 and Uncomment line 8 everything is working as expected, even line 9 also work.

I've checked your measure on dummy data and there is no problem - it works fine. Please, provide some more information: your data sample, desired result, etc. — Darkitechtor

Ryan B. Ryan B. · Accepted Answer · 2022-07-27T16:58:51

I expect this behavior will be explained by the difference between BLANK() and the empty string.

What is most likely happening is that you are getting fowled up by the behavior where a visual will filter out blanks by default, but an empty string will be shown. This is an annoying subtlety, because the format function will return an empty string even when its input is a blank.

FORMAT(BLANK(),"") <> BLANK()

To fix this oddity, you will want to need to add a little code to try to help reinforce that blanks should stay blanks.

AverageX6IntervalLabel** = 
VAR hourNum=INT(divide([Average],60))
VAR minuteNum=MOD([Average],60)

VAR result = FORMAT(hourNum,"#00")&":"&FORMAT(minuteNum,"#00")

RETURN
IF (result = ":", BLANK(), result)

Hope it helps.

Format function in DAX bring the different customer data

1 Answers