Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

great stuff

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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