Forum Discussion
Distinct Values with date filter
Hi,
I would like some help in displaying distinct values for a specified period of time. 'DISTINCT COUNT' works seamlessly without a filter applied. For example let's say I would like to see distinct shops visited in week 35, I should be able to see ShopE only since ShopB and ShopD have been visited in week 29 and week 34 respectively.
Any advice how this can achieved?
| Shop_id | Shop_name | Date_Visited | Week_No |
| 100 | ShopA | 14/7/20 | 29 |
| 101 | ShopB | 15/7/20 | 29 |
| 102 | ShopC | 22/7/20 | 30 |
| 103 | ShopD | 17/8/20 | 34 |
| 100 | ShopA | 21/8/20 | 34 |
| 102 | ShopC | 13/8/20 | 33 |
| 106 | ShopE | 29/8/20 | 35 |
| 101 | ShopB | 23/8/20 | 35 |
| 103 | ShopD | 25/8/20 | 35 |
| 101 | ShopB | 14/9/20 | 38 |
Here is an alternative (table name 'ShopVisits'):
1) Measure to use in the filter panel to filter a tableDistinct visits = VAR PrevVisit = CALCULATETABLE(VALUES(ShopVisits[Shop_name]), FILTER(ALL(ShopVisits[Week_No]), ShopVisits[Week_No] < SELECTEDVALUE(ShopVisits[Week_No]))) VAR Selected = VALUES(ShopVisits[Shop_name]) RETURN COUNTROWS(EXCEPT(Selected, PrevVisit))2) measure to list the new stores visited:
New Shops visited = CONCATENATEX(FILTER(ALL(ShopVisits[Shop_name]), [Distinct visits] = 1), ShopVisits[Shop_name], ", ")And you understand this:
4 Replies
Kaskazi_Network , Try a measure like
Assume a week is selected
countx(values(Shop_id) , if(distinctcount(Table[Week_No]) ,calculate(distinctcount(Table[Week_No]) , filter(all(Table), Table[Week_No]<=selectedvalue(Table[Week_No])))<=0, blank(),1))
Best is that you move week to week table
and try like
countx(values(Shop_id) , if(distinctcount(Table[Week_No]) ,calculate(distinctcount(Table[Week_No]) , filter(all(Week), Week[Week_No]<=selectedvalue(Week[Week_No])))<=0, blank(),1))
Kaskazi_Network Maybe something like the following?
Measure = VAR __Min = MIN('Table'[Date]) VAR __Max = MAX('Table'[Date]) RETURN COUNTROWS(DISTINCT(SELECTCOLUMNS(FILTER(ALL('Table')[Date_Visited]>=__Min && [Date_Visited]>=__Max),"Shop_name",[Shop_name])))Here is an alternative (table name 'ShopVisits'):
1) Measure to use in the filter panel to filter a tableDistinct visits = VAR PrevVisit = CALCULATETABLE(VALUES(ShopVisits[Shop_name]), FILTER(ALL(ShopVisits[Week_No]), ShopVisits[Week_No] < SELECTEDVALUE(ShopVisits[Week_No]))) VAR Selected = VALUES(ShopVisits[Shop_name]) RETURN COUNTROWS(EXCEPT(Selected, PrevVisit))2) measure to list the new stores visited:
New Shops visited = CONCATENATEX(FILTER(ALL(ShopVisits[Shop_name]), [Distinct visits] = 1), ShopVisits[Shop_name], ", ")And you understand this:
- That worked perfectly for me, thanks
