cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

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 an artificial fourth label "missing"? As in, there are no evaluations for this contract, sor this month? 

1 ACCEPTED SOLUTION

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

 

image.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

8 REPLIES 8
Microsoft
Microsoft

Hi @102938974756545

 

Where would you like to show the "Missing" value?  In a new column in the 'Contracts' table?  or somewhere else?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

The end game is a visualisation., let's say a pie graph.

 

The pie, (100%) shows all the evaluations. There will be four categories. draft final or approved..... and > Missing <.

 

One slice of the pie is, for example, "3% of evaluations are missing"

 

 

Hi @102938974756545

 

Sorry this one slipped off my radar.  Did you manage to solve it in the end?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Nope. 

Can you please mock up some sample data that looks like your data and post here.  I'm sure we can get this working for you.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Capture.PNG

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

 

image.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

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

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors