Unique dealer inquiries per part

[WLFRD]

Hello,

I have this table of data and I would like to calculate the unique views per part number. If the same day a dealer views a part twice, it must be counted as one unique view.

Data:

Inquiry date	Dealer	Part
1/1/2022	100100	000125
1/1/2022	100101	000875
1/1/2022	100105	000125
1/1/2022	100100	000125
1/1/2022	100100	000826
2/1/2022	100108	000125
2/1/2022	100175	000125
2/1/2022	100100	000698
2/1/2022	100136	000214
3/1/2022	100100	000214
3/1/2022	100145	000214
3/1/2022	100108	000214
3/1/2022	100108	000587
3/1/2022	100108	000698
3/1/2022	100257	000125
4/1/2022	100100	000125
4/1/2022	100145	000369
4/1/2022	100108	000369
4/1/2022	100108	000369
4/1/2022	100108	000158
4/1/2022	100257	000487
5/1/2022	100100	000489
5/1/2022	100145	000125
5/1/2022	100108	000125
5/1/2022	100108	000125

Result must look like this:

Part	Unique Dealer Inquiries	Date
000125	8	1/1/2022:2, 2/1/2022:2, 3/1/2022:1, 4/1/2022:1, 5/1/2022: 2
000875	1
000826	1
000698	2
000214	4
000587	1
000369	2
000158	1
000487	1
000489	1

Can someone help me out?

Thanks in advance.

[v-yangliu-msft · Accepted solution]

Hi  @WLFRD ,

Here are the steps you can follow：

1. Add Column – Index Column – From 1.

2. Create calculated column.

Count =
COUNTX(FILTER(ALL('Table'),
'Table'[Inquiry date]=EARLIER('Table'[Inquiry date])&&'Table'[Dealer]=EARLIER('Table'[Dealer])&&'Table'[Part]=EARLIER('Table'[Part])
),[Dealer])

count_if =
IF(
    [Count]=2,1,[Count])

min_Index =
MINX(FILTER(ALL('Table'),'Table'[Inquiry date]=EARLIER('Table'[Inquiry date])&&'Table'[Dealer]=EARLIER('Table'[Dealer])&&'Table'[Part]=EARLIER('Table'[Part])),[Index])

Count_min =
IF(
[Index]=[min_Index],[count_if],0)

Unique Dealer Inquiries = SUMX(FILTER(ALL('Table'),[Part]=EARLIER('Table'[Part])),[Count_min])

3. Create calculated table.

Table 2 =
SUMMARIZE('Table','Table'[Part],'Table'[Unique Dealer Inquiries])
Create calculated column.
Date =
CONCATENATEX(FILTER(ALL('Table2'),'Table2'[Part]=EARLIER('Table2'[Part])),[Inquiry date]&"",",")

4. Create calculated table.

Table3 =
SUMMARIZE('Table2','Table2'[Part],'Table2'[Unique Dealer Inquiries],'Table2'[Date])

5. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[WLFRD]

Thanks for your very detailed explanation! Much appreciated! I can use this for several purposes! 🙂

Thanks again for your time!

[amitchandak]

@WLFRD , You can create a measure like this and use

concatenatex(summarize(Table, Table[Date], "_1", count(Table[Part Numer]) , [Date] & ":" & [_1] , ";" )

[WLFRD]

Thanks for your reply. For some reason, the concatenatex measure stops at .....[Date] &.....

Maybe I was not clear enough in my first message. The date in the second table, is not a part of the table. It was just to show how many dealer inquiries have been done on the given dates. It is just there for your reference. 

I think that might be the problem for me right now.