cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Use NA (or N/A) where there is no data!

Hi:

    Does anyone know a way to use "N/A" where the data is missing?

 

pbi na before.png

 

 

pbi na after.png

 

1 ACCEPTED SOLUTION

@Stan-Arabei

 

On the modeling tab you click on New Table and then (for example) use this code:

Date = ADDCOLUMNS ( CALENDAR( DATE(2017,1,1), TODAY()), "DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ), "Year", YEAR ( [Date] ), "MonthNo", FORMAT ( [Date], "MM" ), "YearMonthNo", FORMAT ( [Date], "YYYY/MM" ), "YearMonth", FORMAT ( [Date], "YYYY/mmm" ), "MonthShort", FORMAT ( [Date], "mmm" ), "MonthLong", FORMAT ( [Date], "mmmm" ), "WeekNo", WEEKDAY ( [Date] ), "WeekDay", FORMAT ( [Date], "dddd" ), "WeekDayShort", FORMAT ( [Date], "dddd" ), "Quarter", "Q" & FORMAT ( [Date], "Q" ), "YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ))

 

Then, in the Relationships tab you connect the Date column with the Period column, then create the matrix.

View solution in original post

23 REPLIES 23
Helper II
Helper II

You could do it in the query editor.  Just add an expression to find and replace null values with N/A.  

Resolver I
Resolver I

Yeah, the ISBLANK() function can be the solution in your case, like @JoHo_BI said, but it'd be great to know how your raw data look like in order to create the concrete IF() function. 

 

Regards,

Andris

here is link  to similar solution I provided on another post. Hope it is helpful.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Resolver V
Resolver V

Hi Stan,

 

You can wrap your values in an '=IF(ISBLANK(...

 

So your final formula would look something similar to:

 

=IF(ISBLANK([Measure]),"N/A",[Measure])

 

Hope that helps!

Thanks for your solution:

The values are simply not there.
I would emagine, I'd have to create a table in Power BI with blank values and then utilize your solution of IFBLANK().

 

phase01.png

There are no blanks in my data.

The values are not there at all.

 

Before I convert blanks into any other value, like zero/NA/ or dash, there has to be some data.

 

However, as you can see in my raw data there is no values for 'EAST' during January and March.

You can create a Date/Calendar table, connect it to your main table's Period column, and use its Date column or Month column as the columns of the matrix, use Partners as the rows of the matrix, and create a Measure something like
Amounts = IF(ISBLANK(SUM([Amount]))=TRUE();"N/A";SUM([Amount])), and use this as the values of the matrix.

 

Regards,

Andris

Thank you, Andris:

    Please elaborate on how I can create the Date/Calendar table

@Stan-Arabei

 

On the modeling tab you click on New Table and then (for example) use this code:

Date = ADDCOLUMNS ( CALENDAR( DATE(2017,1,1), TODAY()), "DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ), "Year", YEAR ( [Date] ), "MonthNo", FORMAT ( [Date], "MM" ), "YearMonthNo", FORMAT ( [Date], "YYYY/MM" ), "YearMonth", FORMAT ( [Date], "YYYY/mmm" ), "MonthShort", FORMAT ( [Date], "mmm" ), "MonthLong", FORMAT ( [Date], "mmmm" ), "WeekNo", WEEKDAY ( [Date] ), "WeekDay", FORMAT ( [Date], "dddd" ), "WeekDayShort", FORMAT ( [Date], "dddd" ), "Quarter", "Q" & FORMAT ( [Date], "Q" ), "YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ))

 

Then, in the Relationships tab you connect the Date column with the Period column, then create the matrix.

View solution in original post

 

@andris_ = Genius!!   THANK YOU!

 

display2.png

 

 

 

@Stan-Arabei, I'm glad if I could help you. 🙂

 

You can filter out the empty months, or delete them in query editor, or even can change the parameter in the Date table from today, if it's necessary.

 

Regards,

Andris

@Stan-Arabei although you have solution in place which is correct way to do it but I'm still not sure why formula which I provided will not work and it is working on my end.

 

Are you adding it as a new measure or column? It needt to be  a measure






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





I used a 'New Measure'

sorry for this silly question, does your table name is also Table6 as you showed in formula? Are you on matrix using Total Amount measure?






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Yes. My Table name is also Table6: i renamed it to avoid any differences between your solution and my setup.

 

I did use a Matrix and the 'Total Amount' measure.

 

I don't really see how PBI would display values for the variables that don't exist (in this case, "N/A") unless they are created in a separate table or are already a part of original dataset.

It will work since in measure DAX expression we are telling what to show when value is blank and it is common scenario, it should work and it is 110% working on my end. I'm trying to find out what is the root cause it is not working for you.

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





There is no Value. The Value does not exist.

It's not Blank and it's not zero. The entry is simply not there; like there is no entry for year 2019, for example. unless I create it in a separate table.

 

    PBI cannot aggregate values that don't exist.

here is the image of table6 which I created based on your sample data:

 

table6.PNG

 

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Hi Parry: it doesn't seem to work.

 

display.png

that is fine, did you tried what is suggested






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors