cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## Unique dealer inquiries per part

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?

1 ACCEPTED SOLUTION
Community Support

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

4 REPLIES 4
Community Support

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

Helper III

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

Super User

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

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

Helper III

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.