There was at a time, an attribute for the x-axis or value in a table to ‘Show rows with no data’. Not sure what Microsoft did with this option, but it is no longer available for some reason. It might be it is there if certain conditions are meet?
But, there is another way. The first iteration of this DAX measure returns too many rows.
Sum Plus 0 –>
Sum Plus 0 = sum(SerTg[AdjPts]) + 0
The issue is the Sum data table has a relationship to a parent table that has a relationship with a parent/parent (Snowflake) that has Names and Numbers that do not contain ANY related data, thus lots of rows that do not make sense (it does a FULL OUTER JOIN between tables which shows date that do not exist for the middle table).
It is like have a Sales Order Header without any Sales Detail rows with a join to Customer table.
Thus our use of the SUMX function. This iterates over the possible related rows to find the With No Data rows and substitutes a 0.
SUMX Plus 0 ->
SUMx Plus 0= SUMX( ‘Srv’, Sum(SerTg[AdjPts]) + 0)
Now, we only get the Srv rows that has SerTg rows plus the Srv rows with out data. There are no FULL OUTER JOIN with parent table of Srv.
There is another way, first create an indicator of the Srv rows with no data. We created variables in the DAX Measure to explain the flow:
| ServWithWithoutTags =
// Count the total distinct srvs in the Srvs table VAR totalServs = CALCULATE(DISTINCTCOUNT(Serv[ServId])) // Count how many distinct servs did have tags VAR ServWithTags = CALCULATE(DISTINCTCOUNT(ServTag[ServTagKey])) // Subtract the serv who did have tags from the total to get the number of servs that did not have tags RETURN ServWithTags – totalServs |
Now, we can use this indicator to do a simple addition to get our No Data rows
Adj Pt With NoServer = IF([ServWithWithoutTags] = -1, 0, [Adj Pt Sum])
