Adding 0 to measure returns all rows in the table, ignoring slicers and relationships

[Anonymous]

I am a DAX novice, at best. 

I have a fact table with 15 million rows.  It has a relationship to a dimension table.

I want a distinct count of column XYZ in the fact table, and I want to use a slicer to filter that count by the dimension table.

The distinct count works fine if I only want to show rows where column XYZ in the fact table is not blank.

The issue is when I add " + 0" to my "DISTINCTCOUNT" measure in the fact table.  When I do that, the slicer for dimension is ignored, and the relationship between fact table and dimention table goes out the window, giving me all rows in the fact table, evan though there's a slicer in effect.

This is the formula for my measure: myMeasure:=DISTINCTCOUNT(myTable[columnXYZ]) + 0

How do I make the " + 0 " part respect my slicers and the relationships in the model between fact and dimension tables?

Thanks for reading!

[kampyy]

Even I am getting addition rows in Table when I pull the measure where I am replacing blank with 0.

[Anonymous]

I found some hope by using the "SHOW ITEMS WITH NO DATA" option in Power BI.  But I wish I could make the blank values show up as zeros.  Every time I try to make the blank valuse show up as zeros, it blows up all relationships and slicers.

[Stachu]

is the field that you count also the field that is used in the relationship with the dimension table?

[Anonymous]

No... It is not.  In my measure, I am counting column B in the fact table, and linking the fact table to the dimension table on column A.

I just realized that I can get the count I want by creating a calculated column in the dimension table.  Only problem there is I lose the ability to drill through to the details.

[Anonymous]

Hey Anonymous, did you manage to get a resolution around this? Am stuck too!

[Ashish_Mathur]

Hi,

Why are you adding 0 there?

[Anonymous]

Adding 0 is the tried and true hack to make DAX measures return zero instead of blank.  I am trying to create a report where I see all of my products, and which ones sold, as well as which ones didn't sell.  But when I am adding 0 to the measure, I get all rows (i.e. products in the database) regardless of the slicers applied.  

[Phil_Seamark]

Hi @Anonymous

Just make sure you are using the same field from the slicer table in the table visual and not the field that it joins to in your fact table.

[sahameeda]

Is there a waorkaround for this yet?

[Anonymous]

Thanks for the idea, but it did not work.  I feel like my DAX measure needs some kind of additional function or filter that I am not aware of due to my inexperience with DAX.  Seems like a pretty common problem.  I have a star-shaped model in SSAS, and need to do counts of key fields in my fact table, filtering by values in the dimension tables, and INCLUDE SELECTED DIMENSIONS THAT HAVE 0 RECORDS IN THE FACT.  I'm worried that I'm gonna spend this whole week down this rabbit hole. 

[Stachu]

something like this?
DistCountWithZeros:=IF(ISBLANK(DISTINCTCOUNT('Fact'[XYZ])),0,DISTINCTCOUNT('Fact'[XYZ]))

[Anonymous]

Thanks, but unfortunately the IF / ISBLANK solution provides the same result as adding 0 to the count.  When I add that measure to my table, I get all values in the fact table. when filtering by a dimension.  I'm still trying to find out how to only see the values in my slicer selection in my fact table and also include the blanks.  

[danextian]

Hi @Anonymous,

Have you tried using Show items with no data? This was included in Feb 18 feature summary.

Haven't tried this myself but this can probably help you.

[Taps]

Just wondering if you played around with ISBLANK() function

Thanks