Forum Discussion
creating a white space table
This had me scratching my head!
It never occured to me to ask, do the blank rows actually exist in the data?
I suspect that there is no row in the data for Company = Johnson, Month = May
If there is no row we can't change it's colour because it technically doesn't exist.
How does the data look in the back end before you put it in the visual?
Did you pull the data in an unpivot it by any chance?
if the principal pays then a data point is entered into the system, however if nothing is received then no data point. When I pull in the measure into the table...it is driven by the check value. I would guess that if in Johanson's case they paid every month from January to June and July they paid nothing...I would still see a cell for Johanson for June-December even though there is no data point.
So what I did was go back into my data and entered a line for a few principals active and inactive and entered a value of zero for the commission check (used a mock check number). I was hoping that by doing so and refreshing I would see black.
Advanced Energy Jan and May - (this one is active)
Johanson Dielectrics May, June, July (this is inactive)
Johanson Technology May and July (this is inactive)
After refreshing, I see black only on Johanson and only in July., so that is weird.
emma313823
I Think I have it:
First create a column with all the required months, join this to your month column and use it in the visual instead of the existing month
Create a measure that returns 0 when there is no data, use this in the values
Make the colour measure check for this 0 and the status
Replace with 0 = if(sum(active[Value]) <> BLANK() , sum(active[Value]),0)Colour measure =SWITCH(TRUE(),[Replace with 0] = 0 && CALCULATE(SELECTEDVALUE(active[Principal Status]), all('calendar table'[Calendar Month Name])) = "Active","Pink",[Replace with 0] = 0 && CALCULATE(SELECTEDVALUE(active[Principal Status]), all('calendar table'[Calendar Month Name])) = "Inactive","Black")I've uploaded a test file here: