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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Sylvain74
Helper III
Helper III

Problem with creating DAX measure based on result of Summarize

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"
)        

 

 

1 ACCEPTED SOLUTION
v-jayw-msft
Community Support
Community Support

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.

1.PNG

2.PNG

 

Best Regards,

Jay

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

View solution in original post

2 REPLIES 2
v-jayw-msft
Community Support
Community Support

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.

1.PNG

2.PNG

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Sylvain74
Helper III
Helper III

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.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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