Unexpected display results in a Matrix visual when referencing a Measure containing IF

[Anonymous]

I am getting unexpected results in the displayed Matrix visual when I display a Measure which contains an IF statement.

My sample data:

dimension table  - Customers 

    Cust No - rows for customer numbers A, B, and C

    Cust Name

dimension table - Date Ref

    Date - rows for each and every day for 2019 and 2020

transaction table - Transactions

    Date

    Cust No

    Revenue

    Gross Profit

    [Note: I only have transactions in the table for Cust No = A]

Measures

    Total Revenue = sumx(Transactions, Transactions[Revenue])

    Total Gross Profit = sumx(Transactions, Transactions[Gross Profit])

    Total Gross Profit % = divide([Total Gross Profit],[Total Revenue],0)

Relationships

    Customers 1:many to Transactions linked on Cust No

    Date Ref 1:many to Transactions linked on Date

At this point, in a Matrix visual with Customers[Cust No] for rows, Date Ref[Date] with hierachy Year, Month for columns, and Total Revenue, Total Gross Profit, Total Gross Profit %, everything displays as expected.  As there are only transactions for Cust No = A in the transaciton table, only Cust No = A is displayed (even though there are Cust No B & C in the Customers table).  As there are only transacitons for a few dates in a few months, only the months with transacitons are displayed (even though there are dates for every date across two years in the Date Ref table) This is true whether only displaying totals for Year or expanding levels to also show Month.

Objective: I do want to evaluate the monthly aggregate gross profit percentage in the measure Total Gross Profit % and then display only those items (monthly totals) where the measure results are above (or below) a specific value (e.g., 20%)

Problem: When I create an additional measure Above GP% Target = if([Total Gross Profit %] > 0.20, True, False), and then place this in my Matrix visual, the displayed results of Above GP% Target are correct.  However, the displayed rows expand to include every customer that is in the Customers table, even though there are no transactions for many customers in that table.  And the displayed columns expand to include every month in the Date Ref table, even though there are no transactions for many of the dates in that table.  Further more, if I apply a filter to the visual in the Filters to filter the Above GP% Target based on True or False, the resulting display is not correct at all.  Likewise, if I filter directly on the measure Total Gross Profit to achieve my objective instead of filtering on the additioal measure Above GP% Target, the resulting display is not correct.

I suspect the issue is either something with "context" that I am not understanding - or something with IF statements inside of measures.  Is there any guidance on this problem?

Many thanks,

[lbendlin]

Your measure 

Above GP% Target = if([Total Gross Profit %] > 0.20, True, False)

will result in "False"  for missing values of [Total Gross Profit %].  

To avoid that , check for blanks.

Above GP% Target = switch(true(), isblank([Total Gross Profit %]),blank(),[Total Gross Profit %] > 0.20, True, False)

[Anonymous]

Thanks Ibendlin.  I do see your logic that the simple IF statement will return false anytime the Total Gross Profit % does not have data to calculate (such as dates in the Date Ref table or customers in the Customer with no related transactions).  However, I tried the Switch approach you noted below, and the results are still expanding the matrix with columns (representing all dates in the Date Ref table) and with rows (representing all customers in the Customer table) even when there is no related transaction in the Transaction table.  It looks like this always evaluates to False, except when there is transaction data present and the Total Gross Profit % is above the 20% threshold.  It is like the isblank([Total Gross Profit %]) function does not evaluate True in those situations where no Transactions exist and instead the logic falls through to the exception clause in the switch statement.

Did I misunderstand your suggestion?  Any other ideas?

[lbendlin]

provide some (sanitized) sample data so we can have a closer look.

[Anonymous]

Thanks Ibendlin.

Below is a screen shot of several table visuals.

Blue - the dimision or reference tables

Purple - the facts or transaction tables

Yellow - an example of the expanded information when adding the Above GP% Target to a table visual

Red - the example of th eexpanded rows and columns when adding the Above GP% Target to a martix visual

The second screen shot shows the expected results for rows and columns when the Above GP% Target measure is not included.

Thanks, 

[Anonymous]

Below are the tables and measures.  The output visuals showing the problem are earlier in the thread.

Thanks,

Customers table

Cust No	Cust
A	ABC
B	BCD
C	CDE
D	DEF

Subset of Date Reference table - includes all dates tha match transactions plus some others outside that

Date	Year	QuarterOfYear	MonthOfYear	DayOfMonth
12/15/2019	2019	4	12	15
12/31/2019	2019	4	12	31
1/1/2020	2020	1	1	1
1/2/2020	2020	1	1	2
1/3/2020	2020	1	1	3
1/4/2020	2020	1	1	4
1/5/2020	2020	1	1	5
1/31/2020	2020	1	1	31
2/1/2020	2020	1	2	1
2/15/2020	2020	1	2	15
2/29/2020	2020	1	2	29
3/1/2020	2020	1	3	1
3/31/2020	2020	1	3	31
4/1/2020	2020	2	4	1
4/2/2020	2020	2	4	2
4/30/2020	2020	2	4	30
5/1/2020	2020	2	5	1
5/15/2020	2020	2	5	15

Transaction table ('Margin Details')

Cust No	Date	Rev	Gross Profit
A	1/1/2020	100	11
A	1/2/2020	100	7
A	2/1/2020	100	9
A	2/15/2020	100	13
A	3/1/2020	100	12
A	4/2/2020	100	8

aTotal Revenue = sumx('Margin Details','Margin Details'[Rev])

aTotal Gross Profit = sumx('Margin Details','Margin Details'[Gross Profit])

aTotal Gross Profit % = divide([aTotal Gross Profit],[aTotal Revenue],0)

aAbove GP% Target = switch(TRUE,

ISBLANK([aTotal Gross Profit %]),blank(),

[aTotal Gross Profit %]>0.1,TRUE,

FALSE)

[lbendlin · Accepted solution]

Very interesting.

Here's a "solution".  need to refine it.

Above GP% Target = switch(true(), isblank([Total Gross Profit %]),BLANK(),[Total Gross Profit %] > 0.20, "True", "False")

[Anonymous]

Thanks for the solution.  It is interesting that the text values "True" or "False" work correctly while the logical values of True / True() or False / False() cause the problem outlined below.  I'm interested to see if any have more details on why that is.

[lbendlin]

Agree. The measure also didn't feel very fast, I am sure there are better ways to write it.

[lbendlin]

sorry I wasn't clear. Sample data in usable form (either pasted as table here, or attached). Not screenshots.