topic Summarise and count data if available on all days in Slicer date range in DAX Commands and Tips

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

SriRed123 — Sun, 01 Dec 2019 10:20:26 GMT

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

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

Greg_Deckler — Sun, 01 Dec 2019 17:06:36 GMT

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.

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

SriRed123 — Sun, 01 Dec 2019 17:53:45 GMT

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