How to Calculate Distinct count of Products per store per Week

[SravaniG]

Hi,

I am very new to daxt. I am having requirement like, to calculte distinct count of products per store, per week. 

My query is showing same value for all the rows.

EVALUATE
SUMMARIZECOLUMNS (
    'Store'[StoreName],
    'Time'[Week],      
    TREATAS ( { "Store1", "Store2" }, 'Store'[StoreName] ),   
    TREATAS ({"P1","P2","P3","P4","P5","P6"},'Product'[Product Name] ),
    FILTER(VALUES('Time'[Week]),'Time'[Week]>="Week1" && 'Time'[Week]<="Week3"),
    "UnitS", [Units] ,
    "ProductCount", DISTINCTCOUNT('Product'[Product Name])   
)

Result:

  

Expected Result should look like:

Could you please some one help me on this.

[v-eqin-msft]

Hi @SravaniG ,

According to my understanding, you want to calculate the count of each store and each week,right?

Please create a new table and add a column using the following formula:

Table 2 =
DISTINCT ( SELECTCOLUMNS ( 'Table', "StoreName", [StoreName], "Week", [Week] ) )

Count =
VAR _st = [StoreName]
VAR _w = [Week]
RETURN
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER ( 'Table', 'Table'[StoreName] = _st && 'Table'[Week] = _w )
    )

The final output is shown below:

Here is the pbix file.

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

[amitchandak · Accepted solution]

@SravaniG , Try without unit

EVALUATE
SUMMARIZECOLUMNS (
'Store'[StoreName],
'Time'[Week],
TREATAS ( { "Store1", "Store2" }, 'Store'[StoreName] ),
TREATAS ({"P1","P2","P3","P4","P5","P6"},'Product'[Product Name] ),
FILTER(VALUES('Time'[Week]),'Time'[Week]>="Week1" && 'Time'[Week]<="Week3"),
"ProductCount", DISTINCTCOUNT('Product'[Product Name])
)