Measure to return count of both 0 and blank with conditional logic

[Anonymous]

Hi all,

I have a strange situation that I can't seem to resolve. Hence, I'm here 🙂 I've tried many variations of calculations, but I'm beginning to think it could be a modeling issue. I've tried to simplify the model and merge all into one table for testing

I have a matrix table with sales rep names as rows, along with a second row for the first date that this sales rep had a sale, which is essentially their "entry" into the system and when they become actively tracked. 

I only want 0's to show for the Week Ending buckets in the columns where the week ending date is greater than or equal to the First Date. If they were not in the system at the time, I want blanks to show so it does not skew the other metrics that count the number of weeks which the rep has 0 deals. In the above example, I want blanks for Apr 22 -May 13 buckets, then 0's or actual counts (simply count of rows in the deals table)

DAX:

First Date is calculated column as row in matrix stepped layout turned off so it looks like a continuous table. 

First Date = CALCULATE(MIN(Deals[scheduleddate]), FILTER(All(Deals), Deals[Rep] = earlier(Deals[Rep])))

Week End = Week End = 'Deals'[scheduleddate] + (8- WEEKDAY('Deals'[scheduleddate], 2)) to obtain the Monday. Used as a filter on the report to filter the columns of the matrix.

StartedBefore = IF(MIN(Deals[First Date]) <= MIN('Deals'[Week End]), TRUE(), FALSE())

# of Deals = IF(Deals[StartedBefore] && ISBLANK(COUNTROWS(Deals)), 0, COUNTROWS(Deals))

I have a DimDate table but it wasn't working even with the relationship between DimDate.Date = Deals.ScheduledDate and using the Week End calculation from DimDate instead. 

Thank you in advance for the wisdom.

[d_gosbell]

If I understand correctly you want 3 different possible outcomes from the [# of deals] measure.

• Blank() if the current week is earlier than the start date
• 0 if the week is after the start date but has no deals
• or the count of deals in that week

I would do that using code like the following (using a variable to avoid counting the rows twice)

# of Deals = 
VAR _countOfDeails = COUNTROWS(Deals)
RETURN IF(Deals[StartedBefore] , 
   BLANK(), 
   IF( ISBLANK( _countOfDeals ), 0, _countOfDeals )
)

[Anonymous]

Yes, you are correct.

Thank you for the reply! I've tried your solution with the variable the output still shows:

I've tried removing the relationship to DimDate and changing some things around, but no luck so far.  I either ger all 0's or all blanks.

[d_gosbell]

So it's tricky to debug an expression without access to the model. One thing you could try is returning different constant values to see which branch is returning the blank values. So in the expression below I've replaced the blank() with 1 and the count of deals with 2 so you should then see a mix of 0,1,2 so we should then know what part the expression needs adjusting

eg.

# of Deals = 
VAR _countOfDeails = COUNTROWS(Deals)
RETURN IF(Deals[StartedBefore] , 
   1, 
   IF( ISBLANK( _countOfDeals ), 0, 2 )
)

I'd also suggest that you try putting [StartedBefore] in the matrix to make sure it is calculating as you expect in each cell.

[Anonymous]

The true/false logic seems to only work correctly when I remove the connection between Deals and DimDate:

The Date table I have a Date Key and a Week End field with custom logic to show the ending Monday of the week. It relates to the Deals table on the Deal Date.

The above logic is correct - replace False with BLANK and where it's True, Countrows + 0.

Removing the relationship to date causes the aggregation /split to be wrong, so I need to adjust the # Deals calculation and dates somehow. 

I can send a sample model if need be. Thank you!

[d_gosbell]

---

@Anonymous wrote:

I can send a sample model if need be. Thank you!

---

That would be great, a sample model would really help.