Problem with creating DAX measure based on result of Summarize

[Sylvain74]

Hello,

I have a table listing detailed inventory transactions. When I group those lines by Inventory and Product Group and Style, the sum of the quantity is giving 0 value.

I would like to create a measure called "Is Remaining Stock" returning "Yes" if the sum of inventory quantity > 0 or "No" in the other case.

I created below measure but as soon as I add this mesure in table (columns: Product Group, Product Name, Style, Inventory Location and Inventory Quantity), I got a lot of additional line with blank values or unexpected values.

I don't understand why PowerBI behaves like this since the table design is aligned with the "Summarize" function...

Is Remaining Stock =
IF (
    SUMX (
        SUMMARIZE (
            'SINGA-Physical Inventory',
            Products[ProductGroup],
            Products[Product Code Name],
            Inventory[Style],
            Inventory[InventoryLocation],
            "Inventory Summary", SUM ( 'SINGA-Physical Inventory'[PhysicalInventory] )
        ),
        [Inventory Summary]
    ) > 0,
    "Yes",
    "No"
)        

[v-jayw-msft · Accepted solution]

Hi @Sylvain74 ,

If you want to filter the table with "yes" and "no", you could simply create a slicer table with a column contains "yes" and "no". Use this table as slicer and create a measure like below:

Measure 2 = IF(SELECTEDVALUE(slicer[Column1])=[Measure],1,0)

Then add this measure to visual filter to filter value = 1.

Best Regards,

Jay

[Sylvain74]

By changing a bit my measure by the below one, I get the correct result:

Remaining Stock = 
MINX(
ADDCOLUMNS(
	SUMMARIZE (
	    'SINGA-Physical Inventory',
	    Products[ProductGroup],
	    Products[Product Code Name],
	    Inventory[Style],
	    Inventory[InventoryLocation],
	    "Inventory Quantity Summary", SUM ( 'SINGA-Physical Inventory'[PhysicalInventory] )
	),
	"Is in Stock",
	IF([Inventory Quantity Summary] > 0, "Yes", "No")
	),
	[Is in Stock]
)

The problem is that I cannot use this measure in a slicer 😞

I guess I should create a New column with above logic but the problem is that it does not work....

Any help is welcome...

Thanks.