Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Nikitun
Frequent Visitor

Using numeric literal constant in Power BI DAX formulas

Hi! I am new to DAX and Power Bi. I have a problem with measure when I use literal numerical constants like #3 in figure below. Calculation is correct, but column is filled for dates not existing in data table. Same problem when measure consists in one number (#4). I guess that the problem somewhere in context, but cant find a desicion. How can I correctly do this calculation in DAX? Thanks!

power rangers.png2.png

1 ACCEPTED SOLUTION
Stachu
Community Champion
Community Champion

the problem is with measures #3 and #4

 

for #3 you have

1-DIVIDE(A,B+C+D,0)

which evalueates to 1-0 =1 when B+C+D are = 0 (basically a constant regardless of A,B,C,D)

you need to do it like this:

DIVIDE(B+C+D-A,B+C+D,BLANK())

which evaluates to BLANK() for B+C+D=0 

 

now for #4 I assume you only want to show 1 whenever there is data, so I would do something like this:

IF(ISBLANK([reaches_ratio]),BLANK(),1)

 



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

View solution in original post

1 REPLY 1
Stachu
Community Champion
Community Champion

the problem is with measures #3 and #4

 

for #3 you have

1-DIVIDE(A,B+C+D,0)

which evalueates to 1-0 =1 when B+C+D are = 0 (basically a constant regardless of A,B,C,D)

you need to do it like this:

DIVIDE(B+C+D-A,B+C+D,BLANK())

which evaluates to BLANK() for B+C+D=0 

 

now for #4 I assume you only want to show 1 whenever there is data, so I would do something like this:

IF(ISBLANK([reaches_ratio]),BLANK(),1)

 



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors