How to handle Blank value in a column count in Table

[Anonymous]

Hi!

I want to get a count based on the Project package status and write  a logic to based on condition to set a package status with either On plan, Late, Early or for anything else TBD.

But when I put in my conditions it breaks into late, early, on plan , TBD and blank. I want to include the count of blanks in TBD only, so I have only 4 category for status.

My logic is as follows, where Variance is the difference of days between two dates. Can we do that any ways.

ON/OFF Plan = IF( ('Table'[Variance between  Date ] = 0 ) ,"ON PLAN",IF((' Table'[Variance between date] > 0 && ' Table'[Indicator] <> "Added Post " ),"LATE",IF(('Table'[Variance between  Date] < 0 && ' Table'[Indicator] <> "Added Post " ),  "EARLY","TBD")))

Kind Regards,

Aagni

[Anonymous]

Just adjust this:

[ON/OFF Plan] =
var __var = 'Table'[Variance between Date]
var __ind = 'Table'[Indicator]
return
switch ( true(),

    __var = 0, "ON PLAN",
    __var > 0 && __ind <> "Added Post", "LATE",
	__var < 0 && __ind <> "Added Post ", "EARLY",
	
	"TBD"
)

Best

D

[Anonymous]

Thanks for your reply, the matter of the fact is that the Table 1 where I am creating this column for On /Off plan , I am using this on/off status to see the count of packages on another table which has the Master data of all packages i.e in table 2. While the Table 1 also has the Package number and the relationishp is maintained in Power BI, what I want to do is compare this On/off plan status for the packages on the Table 2. SInvce the number of packages on Table 2 is more than that of Table 1 hence I get all the status correctly , but for the ones missing in Table 1 I need to show it as TBD , but currently its shgowing blank, can I convert these blanks somehow to show as TBD so I can plot it out in the pie chart only as 4 statuses.

Table 1.

Package1   -20      

Package2    0       

Package3    13      

Package4  43267  (Anything starting before 2019 to be considered TBD)

Table 2(Master Data of Packages)

Package1   

Package2    

Package3    

Package4  

Package5

Package6

The Relationship is on Package number.

WHat I want to show in the Power BI table is 

Package1 = "Early

Package2 = On Plan

Package3 = Late

Package4 = TBD

Package5  = TBD (These Package do not exists on Table 1)

Package6 = TBD (These Package do not exists on Table 1)

Help will be geratly appreciated

[Greg_Deckler]

First advice, use SWITCH(TRUE()...), not nested IF. Will make the logic much clearer and cleaner. Second, sample data would help tremendously. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490