Forum Discussion

tnelson's avatar
tnelson
Frequent Visitor
6 years ago
Solved

Get a count of missing/void data

I am trying to create a visualization of outlets that aren't selling particular products but the dataset that I am working doesn't count items not purchased. Is there a way to either count the number...
Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi tnelson ,

     

    In your scenario, we can create calculate table using DAX to show the missing customer and product/Flavor.

     

    MissProduct = 
EXCEPT (
    CROSSJOIN ( VALUES ( 'Table'[Customer] ), VALUES ( 'Table'[Product] ) ),
    SELECTCOLUMNS (
        'Table',
        "Customer", 'Table'[Customer],
        "Product", 'Table'[Product]
    )
)

     

     

    MissFlavor = 
EXCEPT (
    CROSSJOIN ( VALUES ( 'Table'[Customer] ), VALUES ( 'Table'[Flavor] ) ),
    SELECTCOLUMNS (
        'Table',
        "Customer", 'Table'[Customer],
        "Product", 'Table'[Flavor]
    )
)

     

     

    Then you can create the report or advanced measure based on these tables.

     

    If it still doesn't meet your requirement, kindly share your excepted result to me using the fake sample data. Please upload your files to One Drive and share the link here.

     

    BTW, pbix as attached.

     

    Community Support Team _ DongLi
    If this post helps, then please consider Accept it as the solution to help the other members find it more 

v-lid-msft's avatar
v-lid-msft
Community Support
6 years ago

Hi tnelson ,

 

In your scenario, we can create calculate table using DAX to show the missing customer and product/Flavor.

 

MissProduct = 
EXCEPT (
    CROSSJOIN ( VALUES ( 'Table'[Customer] ), VALUES ( 'Table'[Product] ) ),
    SELECTCOLUMNS (
        'Table',
        "Customer", 'Table'[Customer],
        "Product", 'Table'[Product]
    )
)

 

 

MissFlavor = 
EXCEPT (
    CROSSJOIN ( VALUES ( 'Table'[Customer] ), VALUES ( 'Table'[Flavor] ) ),
    SELECTCOLUMNS (
        'Table',
        "Customer", 'Table'[Customer],
        "Product", 'Table'[Flavor]
    )
)

 

 

Then you can create the report or advanced measure based on these tables.

 

If it still doesn't meet your requirement, kindly share your excepted result to me using the fake sample data. Please upload your files to One Drive and share the link here.

 

BTW, pbix as attached.

 

Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more 

tnelson's avatar
tnelson
Frequent Visitor
6 years ago

I copied and pasted that formula and put my own data in and received an error:

 

"The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

 

Any ideas on why I would receive that error and you don't?

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    Hi tnelson ,

     

    I guess that you create a measure using those formula? Actually I created a calculated table.

     

    Best regards,

    Community Support Team _ Dong Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.