Skip to main content
cancel
Showing results for 
Search instead 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

Reply
WLFRD
Helper III
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

 

Capture.JPG

 

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

 

 

Capture1.JPG

 

Can someone help me out?

 

Thanks in advance.

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

Hi  @WLFRD ,

Here are the steps you can follow:

1. Add Column – Index Column – From 1.

vyangliumsft_0-1646380621882.png

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])

vyangliumsft_1-1646380621884.png

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]&"",",")

vyangliumsft_2-1646380621886.png

4. Create calculated table.

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

5. Result:

vyangliumsft_3-1646380621887.png

 

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

View solution in original post

4 REPLIES 4
v-yangliu-msft
Community Support
Community Support

Hi  @WLFRD ,

Here are the steps you can follow:

1. Add Column – Index Column – From 1.

vyangliumsft_0-1646380621882.png

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])

vyangliumsft_1-1646380621884.png

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]&"",",")

vyangliumsft_2-1646380621886.png

4. Create calculated table.

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

5. Result:

vyangliumsft_3-1646380621887.png

 

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

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

 

Thanks again for your time!

amitchandak
Super User
Super User

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

 

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

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.