Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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)
I then have a table which has the total end result count (Wave1 timeline)
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
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
**I could sent the file over as it contains some confidential data, hence i screenshot portion of the data for reference.
Solved! Go to 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)
I'm not totally sure I get what your problem is, but these may help you.
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)
great stuff
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |