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
ClemFandango
Helper II
Helper II

How to count distinct rows in DAX custom column?

Hellloooo

I have the following DAX formula that counts the number of rows based where the date ‘MMYYDD’ (in my calendar table) falls between the min and max dates (table1).

 

Count =

COUNTROWS (

    FILTER (

        ‘Table1',

        [MMYYDD] >= ' Table1' [MinDate]

            && [MMYYDD] < ' Table1'[MaxDate]

    )

)

Table1  
AccNumMinDateMaxDate
ACC5281   31/03/2021   31/03/2024   
ACC528131/03/202131/03/2024
ACC528131/03/202131/03/2024
ACC528131/03/202131/03/2024
ACC528131/03/202131/03/2024
ACC525517/01/202330/11/2023
ACC525517/01/202330/11/2023
ACC525517/01/202330/04/2023
ACC525517/01/202330/04/2023
ACC525417/01/202329/02/2024
ACC525417/01/202329/02/2024
ACC525417/01/202329/02/2024

 

Unfortunately, Table1 includes duplicate account numbers, and this inflates the figures considerably. Is it possible to amend the above formula so it only counts distinct rows from ‘Table1’ where my AccNum is distinct? I am unable to remove duplicates in PowerQuery as my min/max date calculations are created using DAX in ‘Table1’. This is my end output:-

 

Cal1 
MMYYDDCount
01/11/202181201
01/12/202180930
01/01/202280654
01/02/2022   80344   
01/03/202280166
01/04/202279635
01/05/202278999
01/06/202278366
01/07/202277729
01/08/202277210
01/09/202276604
01/10/202275846
01/11/202275394

 

Any help massively appreciated!

 

CF

1 ACCEPTED SOLUTION

VAR __tab = filter(SUMMARIZE('Table1',[column1],...), ...)

RETURN
countrows(__tab)

think that this virtual table is like a pivot so you get 1 line by accnum

View solution in original post

5 REPLIES 5
ClemFandango
Helper II
Helper II

Amazing stuff! Thanks again!!

you're welcome 🙂

in the first 25 days challenge of Curbal, there are quite some examples of such virtual tables... good source of learning...

ClemFandango
Helper II
Helper II

Many thanks marcl1, I really appreciate the response. Creating a VAR virtual table is a completley new concept to me. Do you have any examples of how I would do that? I am just checking that I am following you correctly, would the VAR virtual table condense Table1, providing just unique AccNum's?

VAR __tab = filter(SUMMARIZE('Table1',[column1],...), ...)

RETURN
countrows(__tab)

think that this virtual table is like a pivot so you get 1 line by accnum

marcl1
Helper II
Helper II

Hi,

you can create a VAR virtual table with the function summarize

then you make the countrows on this virtual table...

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.