topic Re: How to handle Blank value in a column count in Table in DAX Commands and Tips

How to handle Blank value in a column count in Table

Anonymous — Wed, 04 Mar 2020 22:14:41 GMT

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

Re: How to handle Blank value in a column count in Table

Greg_Deckler — Wed, 04 Mar 2020 22:44:50 GMT

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

Re: How to handle Blank value in a column count in Table

Anonymous — Sat, 07 Mar 2020 23:06:07 GMT

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

Re: How to handle Blank value in a column count in Table

Anonymous — Tue, 10 Mar 2020 15:32:06 GMT

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