Forum Discussion

emma313823's avatar
emma313823
Icon for Helper V rankHelper V
2 years ago

creating a white space table

All,   I'm struggling with how best to approach this. I thought conditional formatting may help, but I think I will be very limited. Let me explain what I'd like to accomplish in a Power BI matrix ...
Need Help
SamWiseOwl's avatar
SamWiseOwl
Icon for Super User rankSuper User
2 years ago

You need [] around measure names so [CurrYrComm], sorry editing on a phone is hard! 😂

  • emma313823's avatar
    emma313823
    Icon for Helper V rankHelper V
    2 years ago

    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.

     

    WhtSpcColor = Switch(
    true()
    ,SelectedValue(Principals[Principal Status]) ="Active" && [CurrYrComm] = BLANK()
    ,"White"
    ,SelectedValue(Principals[Principal Status]) ="Inactive" && [CurrYrComm]= BLANK()
    ,"Black"
    ,"Pink"
    )
     
    I changed it to this, but it's not working. No cells populate black at all.
     
    WhtSpcColor = Switch(
    true()
    ,SelectedValue(Principals[Principal Status]) ="Active" && [CurrYrComm] = BLANK()
    ,"White"
    ,SelectedValue(Principals[Principal Status]) ="Inactive" && [CurrYrComm]= BLANK()
    ,"Black"
    )

     

     

    • SamWiseOwl's avatar
      SamWiseOwl
      Icon for Super User rankSuper User
      2 years ago

      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?

      • emma313823's avatar
        emma313823
        Icon for Helper V rankHelper V
        2 years ago

        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.