How to GroupyBy to keep filtered values

[woolf]

Hi, I'm fighting with GroupBy/Summarize. I've a imported table with Category, Region, Market, ProductId. I want to filter the table in the dashboard to see the available products in the different shops.

As Examples it should deliver the results:

Example 1: Nothing is selected; number of availble products in total -> COUNTA =7
Example 2: Spain is selected; number of availble products in Spain -> COUNTA = 4
Example 3: Spain AND Shop A is selected; number of available products in Shop A -> COUNTA= 2

But my formula isn't working. I didn't get the expected result.

GroupBy('ImportedTable','ImportedTable'[Category],'ImportedTable'[Region],'ImportedTable'[Shop],'ImportedTable'[ProductID],"Count",CountAX(CurrentGroup(),'ImportedTable'[ProductID]) 

How can i solve it or handle it. Thanx in advance.

Category	Region	Shop	ProductId
PC	Spain	Shop A	1574498
PC	Spain	Shop A	1574498
PC	Spain	Shop B	1574498
PC	Spain	Shop C	1574498
PC	Germany	Shop D	1574498
PC	Italy	Shop D	1574498
PC	Netherlands	Shop E	1574498

[Greg_Deckler]

@woolf COUNTA( 'Table'[ProductId] ) See PBIX attached below signature.

[woolf]

Thanx for the quick answer! For showing the numbers it's works perfect. But how can I implement an additional criteria. "Based on the current active filters show me stocks smaller 1 (Order now) and greater 2 (No further action)."

I've two measures now. Count_1 = COUNTA( 'Table'[ProductId] ) and a second one Count_2 = IF([Count_1]=1,"Order now","No further action")

Why I need it based on the filters? If I've 4 pieces in Spain, I can check the different shops and I've to identify is there a Shop with more then 2 pieces and I can shift it from to another. 

That was the reason why I tried it with new table but the filtered values were not available.

StockCompare=ADDCOLUMNS(GROUPBY('ImportedTable','ImportedTable'[ProductID],"Cnt.",COUNTAX(CURRENTGROUP(),'ImportedTable'[ProductID])),"Stock",IF([Cnt.]=1,"Order now","No further action"))

ProductId	Count_1	Count_2
1574498	2	No further action
1574536	1	Order now
1574641	1	Order now
1574659	2	No further action
1574669	1	Order now
1575612	1	Order now
1575679	2	No further action
1575693	1	Order now

[Anonymous]

Hi @woolf ,

You can do this by simply using an if statement:

Measure 2 = IF([Measure]=1,"order now","No further action")

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.