Adding a MISSING value to a pie chart

[me1982]

In powerBI I have a table of elements and a table of properties (key and value for each propery) . Properties are linked to elements using an element ID.

Not all elements have all the properties.

Sample data:

Elements Table

Element-ID	Element-Name
1	First element
2	Second Element

Properties Table:

Property Key	Property Value	Element-ID
Color	Red	1
Color	Blue	2
Size	Small	1

I want to show a pie chart with a specific property (For instance shirt size) and its values and for elements that don't have that property I want to see the value Missing or blank.
My current naive solution shows only elements that have that property linked to them.

In the example above the pie chart will have only one 360 degrees slice of the size small when what I want to see are two slices (180 degrees each) one with the size small and the other with the size Missing.

I also tried using a measure to retrieve the first property value or Missing if no value found. The problem is I can't use the measure as a pie chart legend.

[v-kongfanf-msft · Accepted solution]

Hi @me1982 ,

As you said, measure cannot be applied to pie charts. try the following calculated column.

result_ =
VAR elementID = Elements[ElementID]
VAR shirtSize =
    CALCULATE (
        MAX ( 'Properties'[Value] ),
        'Properties'[Key] = "Shirt Size"
            && 'Properties'[ElementID] = elementID
    )
RETURN
    IF ( ISBLANK ( shirtSize ), "Missing", shirtSize )

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[me1982]

Thank you.

This seems to give the correct result. However it does require to add a column for each propery key.

Isn't there a simpler way?

[v-kongfanf-msft]

Hi @me1982 ,

For now, probably creating a calculated column is the best way to go.

Best Regards,
Adamk Kong

[gmsamborn]

I would like to help but I'm a little unclear about your requirements.

Can you show the following?

1)  Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

2) Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

3) Please explain how you would expect to get from step 1 to 2.

4) If possible, please show your past attempts at a solution.

I hope this helps.