cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
d1x0nl30ng
Frequent Visitor

How to create a calculated distinct count if column (referencing another table)

I have a master progress table (Report1) which is dynamic (records being added regularly, and can have duplicate data for few columns~ *:* relationship) (to be very specific, SME, Manufacturer (Import Id) & Product (Import ID) could be duplicated in multiple rows)

Report 1 screenshot.JPG

 

I then have a table which has the total end result count (Wave1 timeline)

Wave1 timelines screenshot.JPG

 

How do i create a calculated column on the (Wave1 timeline) by counting the unique records of Product(Import ID) from Report1, where the 'Report 1'[Manufacturer (Import ID)] matches 'Wave1 timelines'[Vendor]

 

I currently did a manual workaround by creating another worksheet from Report1 file by using below excel function, but this is killing my file as it's already huge in nature and the formula calculation is killing it even further, hence thought of using calculated columns

 

Excel workaround formula.JPG

 

I know i probably need to create a distinct manufacturer table to link the relationship between Report 1 & Wave1 timeline tables, but am struggling with the function or formula for the distinct count in DAX

Relationship.JPG

 

**I could sent the file over as it contains some confidential data, hence i screenshot portion of the data for reference.

1 ACCEPTED SOLUTION

Thanks, i removed the MAX from the function and worked accordingly as what i expected.

(Ignore the total count, i have a space in one of the data, this function worked even better detecting that as same)

 

Compare excel and dax formula.JPG

View solution in original post

3 REPLIES 3
stretcharm
Memorable Member
Memorable Member

I'm not totally sure I get what your problem is, but these may help you.

 

http://sqlblog.com/blogs/marco_russo/archive/2010/02/09/how-to-relate-tables-in-dax-without-using-re...

 

http://www.daxpatterns.com/distinct-count/

 

https://powerpivotpro.com/2017/02/uses-for-new-table-feature-power-bi/

 

Maybe you can create a summary table and join this to the Wave 1 timelines and add a Measure to count disctict products.

Manufacturer Products =
ADDCOLUMNS (
    SUMMARIZE (
        'Report 1',
        'Report 1'[Manufacturer (Import ID)],
        'Report 1'[Product (Import ID)]
    ),
    "ReportLines"CALCULATE ( COUNTROWS ( 'Report 1' ) )
)

Or you can do it in one pass with something like this.

 

DistinctCount =
CALCULATE (
    DISTINCTCOUNT ( 'Report 1'[Product (Import ID)] ),
    FILTER (
        SUMMARIZE (
            'Report 1',
            'Report 1'[Manufacturer (Import ID)],
            'Report 1'[Product (Import ID)]
        ),
        'Report 1'[Manufacturer (Import ID)] = MAX ( 'Wave1 timelines'[Vendor] )
    )
)

 

 

 

Thanks, i removed the MAX from the function and worked accordingly as what i expected.

(Ignore the total count, i have a space in one of the data, this function worked even better detecting that as same)

 

Compare excel and dax formula.JPG

View solution in original post

great stuff

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.