Forum Discussion

102938974756545's avatar
102938974756545
Regular Visitor
8 years ago
Solved

How do I label missing data?

Two tables involved: Contracts and Contract Evaluations.  One Contract has many evaluations (one each month)  An evaluation can be one of three types - draft final or approved.  How do I create ...
  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    Hi 102938974756545

     

    I added a calculated column to my 'Contracts' table using the following code (I have attached a PBIX file)

     

    Type = 
VAR x = CALCULATE(MIN('Evaluations'[Type]),RELATEDTABLE('Evaluations'))
RETURN 
    IF(x="","Missing",x)

    Then dragged the new column to a pie chart twice as follows

     

Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
8 years ago

Hi 102938974756545

 

I added a calculated column to my 'Contracts' table using the following code (I have attached a PBIX file)

 

Type = 
VAR x = CALCULATE(MIN('Evaluations'[Type]),RELATEDTABLE('Evaluations'))
RETURN 
    IF(x="","Missing",x)

Then dragged the new column to a pie chart twice as follows

 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi, 

 

I was hoping you could help me with this. I've attempted to follow your steps and alter them to fit into my current dashboard however I keep getting the error:

 

DAX comparisons operations do not support comparing values of type integer with values type text. Consider using VALUE or FORMAT function to convert one of the values. 

 

I've tried to create this listing in two ways as following:

 

Attempt 1 = Var x = CALCULATE(SUM('Table1'[Custom Col1]) + SUM('Table1'[Custom Col2]) + SUM('Table1'[Custom Col3])

RETURN IF(x="",0,x)

 

Attempt 2 = VAR x = CALCULATE('Table1'[Measure1])

RETURN IF(x="",0,x)

 

The Custom Columns are these formulas:

IF('Table1'[Name Text format] = RELATED('Lookup Table'[Current Name Text Format]),(IF('Table1'[Date]=MAX('Tabe1'[Date]),1,0)),0)

 

And the others are like that just with their own lookup tables

 

The Measure formula is as follows:

 

CALCULATE(SUMX('Table1', 'Table1'[Custom Col1] + 'Table1'[Custom Col2] + 'Table1'[Custom Col3]), FILTER('Table1', 'Table1'[Date] = MAX('Table1'[Date])))

 

Basically what I am trying to achieve is have a matrix model listing peoples names and relevant tasks. I want to be able to track the dates they completed the tasks for the current quarter and previous. This table will then be colour coded accordingly. I've managed all this however the colour coding doesn't work. 

 

Currently I have managed to get some of the matrix model to not show a colour if:

- New joiner their 'previous' will have no colour

- Leaver their 'current' will have no colour

- Mover their 'current/previous' will have no colour when filtered down to individual teams

 

However this doesn't work if there is no data. For example for a joiner their 'previous' date will have no colour ONLY if they have a 'current' date. If they have never completed the task then both current and previous will have a red colour (which is wrong!). 

 

I know you're going to say go to conditional formatting and switch it so that all blanks is white. Again this is wrong because I also need to track if someone has never recorded doing tasks even though they've been in the department for a while. 

 

Currently I have multiple custom columns that complete the lookups as above and allocate a number, I then have an IF statement for colour which is:

 

IF('Table1'[Measure2] >=90 && 'Table1'[Measure1 as above] =3,1,

IF('Table1'[Measure2] <90 && 'Table1'[Measure2] >0 && 'Table1'[Measure1 as above] =3,2,

IF('Table1'[Measure2] <=0 && 'Table1'[Measure1 as above] =3,3,

IF('Table1'[Measure1 as above] ❤️ && 'Table1'[Measure1 as above] = BLANK(),4))))

 

I think my formulas don't because there is no data, hence trying to plug that. Don't get me started on when a new tasks pops up (thankfully not often) and there is no data for anyone! I hope this all made sense. I'm happy to share sample data if you need it. 

 

Thanks!

E