Visual that looks at fields across multiple columns

Hey zbeg ,

as far as I know, there is no such visual that visualizes things that are not there.

For this reason, you might consider adding a calculated column to your table using this DAX:

is missing = 
--var missingCrust = IF( LEN( TRIM('Table'[     Crust] ) ) = 0 ,  "Crust," ) 
var missingCrust = IF( ISBLANK( 'Table'[     Crust] ) || LEN( TRIM('Table'[     Crust] ) ) = 0 ,  "Crust," )var missingSauce = IF( ISBLANK( 'Table'[     Sauce] ) || LEN( TRIM( 'Table'[     Sauce] ) ) = 0 , "Sauce," )
var missingCheese = IF( ISBLANK( 'Table'[     Cheese] ) || LEN( TRIM( 'Table'[     Cheese] ) ) = 0 , "Cheese," )
var missingTopping = IF( ISBLANK( 'Table'[     Topping] ) || LEN( TRIM( 'Table'[     Topping] ) ) = 0 , "Topping," )
var thisismissing = missingCrust & missingSauce & missingCheese & missingTopping
var lastcharacter = RIGHT( thisismissing , 1 )
return
IF( lastcharacter = "," , LEFT( thisismissing , LEN( thisismissing ) - 1 ) , thisismissing )

The DAX looks more complicated than what might be really needed, but this is due to the spaces that have been added to the single columns from the data you provided, and I wasn't that motivated to clean the data 🙂

Nevertheless, here is a screenshot from a table visual, of course, you can just use the columns OrderID and "is missing":

Hopefully, this provides what you are looking for.

Regards,

Tom

zbeg 

You can use a table visual and keep only the order ID, and add this measure:

Still Needs = 
VAR __ItemsFound =  
    {
        ("Crust",MAX(Pizza[Crust])),
        ("Sauce",MAX(Pizza[Sauce])),
        ("Cheese",MAX(Pizza[Cheese])),
        ("Topping",MAX(Pizza[Topping]))
    }

VAR __ItemsNeeded = 

CONCATENATEX(
    __ItemsFound,
    IF( 
        [Value2] = BLANK(),
         [Value1] & ","              
    ),BLANK()
)

VAR __LENGTH = LEN(__ItemsNeeded)-1

RETURN

IF(
    __LENGTH <> -1 , 
    LEFT(__ItemsNeeded, __LENGTH)
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn