Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
Many thanks. The measure is working exactly as per my requirement.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |