Summarise and count data if available on all days in Slicer date range

[SriRed123]

HI,

I am new to power BI and need help with below query.

I have a table with below data. The result should summarize the Data at product and get the count of distinct locations if the location present on all days selected in the slicer data range.

For Date range 1-DEC-19 to 1-DEC-19 result should be

A   3

B  3

For Date range 30-Nov-19 to 1-DEC-19:

A    2

B    1

For Date range 29-Nov-19 to 1-DEC-19:

A    1

B    0

Product	Location	Date
A	1	1-Dec-19
A	1	30-Nov-19
A	2	1-Dec-19
A	2	30-Nov-19
A	2	29-Nov-19
A	3	1-Dec-19
B	1	1-Dec-19
B	1	30-Nov-19
B	2	1-Dec-19
B	2	29-Nov-19
B	2	28-Nov-19
B	3	1-Dec-19

Sql query for producing similar result

SELECT Product, count(distinct Location) FROM (

SELECT Product,Location,count(*) cnt FROM #temp

WHERE Date between <SlicerStartDate> and <SlicerEndDate>

GROUP BY Product,Location

Having count(*) = (SELECT Datediff(Day,MAX(<SlicerEndDate>), MIN(<SlicerStartDate>)) + 1)) A

GROUP BY A.Product

[Greg_Deckler · Accepted solution]

Probably something along the lines of:

Measure = 
VAR __count = COUNTX(DISTINCT('Table'[Date]),[Date])
VAR __table = SUMMARIZE('Table','Table'[Location],"Rows",COUNTROWS('Table'))
VAR __table1 = FILTER(__table,[Rows] = __count)
RETURN
COUNTX(__table1,[Location])

See attached.

[SriRed123]

Many thanks. The measure is working exactly as per my requirement.