Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
tnelson
Frequent Visitor

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 of missing items or a way to add in a static dataset that has the zeros and use that to count the missing items?

 

Any help is appreciated!

1 ACCEPTED SOLUTION

Hi @tnelson ,

 

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

Get-a-count-of-missing-void-data-1.pbix.png

 

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

 

Get-a-count-of-missing-void-data-2.png

 

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

 

Get-a-count-of-missing-void-data-3.png

 

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 

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.

View solution in original post

5 REPLIES 5
v-lid-msft
Community Support
Community Support

Hi @tnelson ,

 

You can create such a measure to count the blank or zero value in a table. If it doesn't meet your requirement, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

 

MissingCount =
COUNTROWS ( ALL ( 'Table' ) )
    - COUNTROWS (
        FILTER ( ALL ( 'Table' ), 'Table'[Value] <> 0 || 'Table'[Value] <> BLANK () )
    )

 

Get-a-count-of-missing-void-data-0.pngGet-a-count-of-missing-void-data-1.png

 

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 

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.

Hi @v-lid-msft,

 

Thanks for the reply. That doesn't quite give me what I am looking for. Unfortunately, the data that I have is confidential but I can provide some sample data. 

 

 

    Customer        Product               Flavor       QTY
       1           Coca-Cola              Vanilla      13
       1            Pepsi                  Cherry       1
       1           Powerade             Fruit Punch     7
       13          Powerade             Fruit Punch     18
       13          Coca-Cola              Vanilla       12

So, this sample dataset shows that customer 1 has purchased 3 different products but customer 13 has only purchased 2. In this case, since customer 13 has not purchased any Pepsi, the dataset does not show Pepsi. Is there a way to count that Pepsi as missing so I can show it on a void report of customers that haven't purchased Products/flavors?

 

Hi @tnelson ,

 

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

Get-a-count-of-missing-void-data-1.pbix.png

 

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

 

Get-a-count-of-missing-void-data-2.png

 

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

 

Get-a-count-of-missing-void-data-3.png

 

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 

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.

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?

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.

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.