Forum Discussion
creating a white space table
Hi...got it to work, however here are a couple of screenshots, something seems off.
Active principals went all pink - no payment cells went white (so that's great), but the black should show for all princpals with no payment (blank cell) status of inactive. So in the 2nd screenshot Johanson, Northern Tedh and Wakefield should show black background in for the white cells.
I went back and looked at the DAX. In line 5 for the inactive status...&& is bringing back a single result right?
So if inactive and current year commisison measure is blank, then make the cell black, else pink?
I think I need to drop the pink altogether. My original two objectives...
1. I need to have any cell where a principal has skipped a payment, but is a status of Active be white - so no change or color. I THINK THE FIRST PART DOES THE TRICK.
2. I need to have any cell where a principal has not paid (cell is blank) with a status of Inactive be colored black.
How would I modify this to remove the pink and just be black?
This is what is being used in the measure now.
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: