March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Below is the source coloumn and goal is to count 1 if the unique items for ID exceeds 2 over a week. Here in Week1 The unique items were 2 so it will be considered 0 and in week2 the unique items exceeded 2 and will be counted as 1.
ID | Item | Week |
100 | Shoe | Week1 |
100 | T-Shirt | Week1 |
100 | T-Shirt | Week1 |
100 | T-Shirt | Week1 |
100 | Shoe | Week2 |
100 | T-Shirt | Week2 |
100 | Cap | Week2 |
100 | Shoe | Week2 |
100 | Belt | Week2 |
So here unique items for ID exceeded in Week 2 So the result will be below
ID Result
100 1
Solved! Go to Solution.
@datadog_ab
Create a measure as follows:
Result =
COUNTROWS(
FILTER(
ADDCOLUMNS(
SUMMARIZE( Table01 , Table01[Week] ),
"@count" , CALCULATE( DISTINCTCOUNT( Table01[Item] ) )
),
[@count] > 2
)
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@datadog_ab
Create a measure as follows:
Result =
COUNTROWS(
FILTER(
ADDCOLUMNS(
SUMMARIZE( Table01 , Table01[Week] ),
"@count" , CALCULATE( DISTINCTCOUNT( Table01[Item] ) )
),
[@count] > 2
)
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
User | Count |
---|---|
117 | |
77 | |
58 | |
52 | |
46 |
User | Count |
---|---|
171 | |
117 | |
63 | |
57 | |
51 |