Blank row in DAX

VALUES and DISTINCT are two functions that differ in how they could add a blank row to the result (or not).
Article and download: sql.bi/245557?aff=yt
How to learn DAX: www.sqlbi.com/guides/dax/?aff=yt
The definitive guide to DAX: www.sqlbi.com/books/the-defin...

[@piesogrodnika572]

"it's breaking the beaty of your report" - I will make a Notification sound of that :D

[@AdiCristea]

These videos are just gold, I think I've said this in the past, but after each video you guys are uploading I am just sat in my chair wowed - "why didn't I think of that, seems pretty logical". Thank you, Alberto!

[@jschwendt]

I wish you could superlike a video. This is one of the best KZbin tutorial videos I've ever seen. The fact that Power BI doesn't flag this with at least a warning message is criminal. Thank you for putting this out!

[@CJ-jc8tn]

Superb tutorial as always. Taking the mystery out of DAX one video at a time. Thank you!

[@RobertoStaltari]

First time I listen to such a clear and simple explanation for blank rows.

[@sue_bayes]

This is really useful and can have such an impact on the final results. Thank you for such a clear explanation.

[@Gustavo-Santana]

Thank you for the explanation Alberto , incredibly didactic! Regards from Brazil

[@powerbiisrael6819]

Thanks a lot, dear Alberto. All the best! From Shlomi (Israel)♥️

[@samuelmanseau6905]

Cristal clear and very usefull ! Thanks a lot for this excellent video and your deep explaination of Dax.

[@paravej]

Thanks Alberto Your all videos are eye opener... Thanks a lot

[@alvarorodriguezlasso]

You are the best: "BRAVO". Regards from Cali-Colombia.

[@heniekheniek1781]

Awesome, this is some solid, useful knowledge! Good job!

[@aleksanderstrommer7677]

I was just wondering this when this video was published!

[@atinmathur951]

Thanks for the informative video. Is there a way to add a text value like "Non-Related" or "Unknown" instead of blank in the color column in given example.

[@smartdata678]

Telepathy or Truman show or Matrix - just yesterday my report started to show blanks... Many thanks. I am a big fan!

[@igordemetriusalencar5861]

Awesome!! I have a datamodel in wich is very important to know and count Blank. Verry informative!! Thank you!

[@matthewdufty606]

Very good. Got this exact issue. Now I can fix it. Thank you ⭐

[@frankgovers8420]

Create a check from sales to products, with related. I always create it to check fact to dimension

[@ajaaskelainen]

Very granular explanation! Thank you!

[@susantakumar8330]

Awesome content. Thank you Alberto for sharing.

[@mikar636]

Just Amazing Video!! Thank you Alberto!!

[@pacogucci6329]

Clear & shinny

[@Dhrittinagpal]

Thank you so much for clarifying it so well..

[@gulmohar5441]

Thank you so much for the video, really informative

[@pravinupadhyay2046]

Nice one. I have also read this in your article before.

[@farmanalishah8836]

Many thanks Alberto. 👍

[@HeliSal700]

As usual, DAX can be mind-bending. Thank you for this informative content. Keep going, please

[@sbn4862]

Fantastic explanation , that's why i always have incorrect results in excel, Thank you

[@EricaDyson]

So so so so so useful.. thank you so much.

[@Luis.C.Canchan]

Muchas gracias Alberto

[@roxaniramirezsanchez5198]

How could i delete rows in a table or chart, if those rows have blank values in some columns. Thanks for the video!

[@danielakiokataoka7395]

Hi. I have a similar problem. I have a table with colors (green, yellow, orange, red, blank) in rows and revenue in the column and I need to create a percentage of the total. I used ALL to create a total and it works well but when I filter only one color in the table the total does not consider the blank value.

[@vincenzomascia4422]

Very interesting, thank you very much :)

[@dariuszspiewak5624]

From this it kind of follows that having blank rows, or even partially blank rows, in dimensions in a model where there are referential integrity problems could be totally confusing and almost impossible to handle. Because how would you then differentiate between the legitimate blank row and the artificial one?

[@kadirkkkk]

You’re king 😀👑

[@user-jy7hd8ku3s]

Do you Know how I can archive values I produce daily in power bi. I need to produce a date table with values which are produce d daily but their are only available the current day. The previous values had to be archived

[@carmelomartelli5754]

In your example, how do you eliminate the empty line (color) with sales amount 5.860.066,14 from the visible object. Thank you very much

[@NoShadowOfDoubt1]

Very nice ... thank you

[@mirrrvelll5164]

Do functions DATESINPERIOD and COUNTROWS creating one blank row even if data set is without any of them?

[@danielakiokataoka7395]

Is it possible to make a similiar video but instead of using countrows to create a percentage use the sales amount? And then, filter one color to show only one color in the table. Tks

[@Darknesslc3]

in technical terms, make sure there is no referential integrity issue, happens on bad data model/design

[@rajasekharvadde6536]

How can we avoid showing the blank row from product from the table visual

[@Bharath_PBI]

Great. Thank you Yes first option would be to not have the invalid relationships. I use DAX studio to check for invalid relationships. A question, if we make the relationship to use inner join instead of left outer (which is default) , it solve the blank row? Side effects of this would be that the report will never show the complete total amounts available in the underlying data source..

[@SQLBI]

You cannot enforce a "inner join" in DAX - you can apply that kind of join while importing/transforming data, but it is up to you to evaluate what to do and manage the side effects of that.

[@Bharath_PBI]

@@SQLBI sorry for not being clear. When we set relationship between sales and product table, if we enforce referential integrity, which behaves as inner join. Will this eliminate blank row?

[@SQLBI]

Enforce referential integrity only works for DirectQuery, not for imported tables. In DirectQuery yes, you eliminate blank rows BUT you eliminate transactions you might want to see - hiding data errors is almost never a good idea!

[@Bharath_PBI]

@@SQLBI Yes, I agree. First choice is , Better to have the quality data (certified dataset) to avoid imperfections and get accurate results which can be used for decision making.

[@venkatakrishnagangavarapu2131]

How to hide blank rows in model ? pls let us know

[@1yyymmmddd]

Thank you for explanation. However how does it happen when date table gets blank values? For example, when I put up a slicer on Date(Year) I always get a blank row.

[@SQLBI]

Probably because you have dates in your data that are outside the range of dates included in the Date table.

[@1yyymmmddd]

@@SQLBI I understand that blank values can appear in expanded table. But I put dates from the Dates table into slicer. Therefore I wouldn't expect blank year to appear.

[@SQLBI]

The slicer shows the data you have in the Date table, including a blank value if the relationship is invalid (unreferenced dates on the many side).

[@minstreltokunbo]

Why is the color column still blank with a sales amount???

[@anuragbawankar685]

Thanks , Hoe can we remove Blank from Slicer/Filter ?

[@edissonalmanza2072]

Is it possible to rename that blank row, for example a "Missing" ?

[@SQLBI]

No. You need to clean up your data upfront and create a row to connect with the "invalid rows", which become valid from a Power BI perspective.

[@zxccxz164]

But how do you handle all these calcs when you do best practice modeling by putting in a dummy value for missing value. ie *NoColor ,*NoProduct. Somehow you have change all your distinct counts to EXCLUDE *NoColor. It is not as simple as it one would think to create one measure that handles the measure whereever you put in on a visual.

[@elrevesyelderecho]

10:25 Why PBI doesn't have a feature called "duplicate measure" / "duplicate column"?

[@SQLBI]

Because this idea needs more votes: ideas.powerbi.com/ideas/idea/?ideaid=002a6710-53f6-44c0-96d9-5a87727bb59b

[@elrevesyelderecho]

@@SQLBI 2016?? Come on!! 😱 This a basic feature that lots of software including Microsoft ones have with just a right click on the mouse. Here we here 2020! And we still waiting for it. I don't want to use Tabular Editor for it and just one right click like in Excel.

[@SQLBI]

We totally agree - and the lack of many features for model development is the reason why a tool like Tabular Editor editor exists. Please, forward your disappointment to Microsoft, they already know our ones and they don't read these comments!

[@zeisslaimen1]

Alberto is very busy recently :)

[@atanasprodatanasov9233]

Watching Alberto explaining this seems like a joke... Having it in real life, and working with PBI for less than an year makes me want to become a farmer instead of business analyst. Question: do you provide individual training?

[@SQLBI]

You can find our training offer here: www.sqlbi.com/training/

[@PeterKontogeorgis]

Stick with it, DAX makes everyone feel like that initially!

[@Hammett1980]

I do not understand why you call an "invalid relathionship" when there are less colors in the fact table than in the dimension table. To me that is a perfectly valid and functional relationship, and I do understand the blank row for the color column, but not for the rest (unless they are in the same scenario). If there are the same categories in the dimension table and the fact table, it makes no sense to have a blank in the slicer for category.

[@SQLBI]

The blank row is added if a single product is referenced in the fact table and does not exist in the dimension. This involves all the columns of the same dimension at that point. In general, the "invalid" state of a relationship exists when one or more values on the "many" side do not have a correspondent value on the one side. The presence of a blank value on the "many" side always generate an "invalid" state because blank cannot be used for a relationship.

[@zxccxz164]

@@SQLBI There are also cases where there is a NULL value in the FACT table.....I don't see any blogs on best practice modeling of inserting dummy value in FACT table that links to the same dummy value in the dimension table. And just as important the IMPACT on writing all measures to correctly count the values. ie You have to EXCLUE the dummy value!!!