Forum Discussion

Dunner2020's avatar
Dunner2020
Post Prodigy
4 years ago
Solved

Removing duplicates using measure

Hi there,

I have two tables: dates and Authorisation. Authorisation table contains information about individuals holding practice certificates which also includes the Authorisation effective date (i.e. Start date of practice certificate) and Authorisation expiry date (i.e. end date of practice certificate ).

I want to create a measure that counts individuals having active practice certificates (i.e. not expired) and displays the measure in a line graph where the x-axis contains the fiscal years (i.e. from FY21 to FY22). So I wanted to show the total no of individuals holding active practice certificates each year.

Authorisation contains information of multiple practicing certificates (i.e. active and expired) of an individual. I want to show the total count of individuals holding active practicing certificates in previous and current fiscal years. For example, for FY17 the logic of picking up holding active practice certificate should be that effective date should be less than or equal to 30-6-2016 and expiry date should be greater than or equal to 1-07-2016

 

I wrote following measure:

 

Active Practice Certificates =

CALCULATE(COUNTROWS(Authorisation),

FILTER(VALUES(Authorisation[Authorisation_Effective_Date__c]),Authorisation[Authorisation_Effective_Date__c] <= MAX(Dates[Date])), FILTER(VALUES(Authorisation[Authorisation_Expiry_Date__c]),Authorisation[Authorisation_Expiry_Date__c] >= MIN(Dates[Date])), Authorisation[Authorisation_Status__c] = “Authorisations Issued” )

 

The above measure produces following output:

 

 

The problem with the above measure is that it calculates some records twice when the license expired and renewed in the same year as shown in the red-colored circles (above).

 

So I made some modification in the measure and the modified measure looks as follow:

Active Practice Certificate -v2 =
CALCULATE(DISTINCTCOUNT(Authorisation[Individual__r.Registration_Number__c]),
FILTER(VALUES(Authorisation[Authorisation_Effective_Date__c]),Authorisation[Authorisation_Effective_Date__c] <= MAX(Dates[Date])), FILTER(VALUES(Authorisation[Authorisation_Expiry_Date__c]),Authorisation[Authorisation_Expiry_Date__c] >= MIN(Dates[Date])), Authorisation[Authorisation_Status__c] = “Authorisations Issued” )

 

When I tried to use the modified measure as a filter in table visual, it takes a lot of time to load the data and duplicated record issue still exist.

Could anyone help me how can I fix the issue?

 

Sample here

Need Help
Show and Tell
Tips and Tricks
  • Icey's avatar
    Icey
    4 years ago

    Hi Dunner2020 ,

     

     

    I want to create a measure that counts individuals having active practice certificates (i.e. not expired) and displays the measure in a line graph where the x-axis contains the fiscal years (i.e. from FY21 to FY22). So I wanted to show the total no of individuals holding active practice certificates each year.

    Authorisation contains information of multiple practicing certificates (i.e. active and expired) of an individual. I want to show the total count of individuals holding active practicing certificates in previous and current fiscal years. For example, for FY17 the logic of picking up holding active practice certificate should be that effective date should be less than or equal to 30-6-2016 and expiry date should be greater than or equal to 1-07-2016

     

    Based on your description, for FY15, the filter conditions should be:

    1. effective date <= 30-6-2014 = MIN ( Dates[Date] ) - 1  => effective date < MIN ( Dates[Date] )
    2. expiry date >= 1-07-2014 = MIN ( Dates[Date] )  => expiry date >= MIN ( Dates[Date] )

     

    So, create your measure like so:

    Active Practice Certificates - v3 = 
CALCULATE (
    COUNTROWS ( Authorisation ),
    FILTER (
        Authorisation,
        Authorisation[Authorisation_Effective_Date__c] < MIN ( Dates[Date] )
            && Authorisation[Authorisation_Expiry_Date__c] >= MIN ( Dates[Date] )
            && Authorisation[Authorisation_Status__c] = "Authorisations Issued"
    )
)

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • speedramps's avatar
    speedramps
    Super User
    4 years ago

    You DAX easure is slow because

    1) you are including the MIN and MAX in the FILTER comand.
    The FILTER comand is an iterator which means this will calc the min and max for every record.
    Also use VAR to calculate MIN and MAX once as a varaible and then use the varaible in the FILTER  


    2) You use 2 FILTER command instead of using the && command.
    This will do 2 passes of your dats instead of one.

    3) Try brea you DAX into steps. 
    Then you can use can test each step to see which is slow
     

    Try this and leave kudos and accept the solution

    mymeasure =

    // calculate the min and max once. never put them in a filter command which iterates for ever records
    VAR startofperiod = MAX(Dates[Date])
    VAR endofperiod = MAX(Dates[Date])

     

    // use the && to create A subset using one instead of two filters. Thus doing just one instead of two passes of your date.
    VAR mysubset =
    FILTER(Authorisation,
    Authorisation[Authorisation_Effective_Date__c] >= startofperiod &&
    Authorisation[Authorisation_Effective_Date__c] <= endofperiod)


    // get a unqiue list of the registration numbers for the subset
    VAR mylist =
    CALCULATE(
    VALUES(Authorisation[Individual__r.Registration_Number__c]),
    mysubset)


    RETURN
    // count the rows in the unqiue list of the registration numbers
    COUNTROWS(mylist)

     

    • Dunner2020's avatar
      Dunner2020
      Post Prodigy
      4 years ago

      speedramps , Thanks for your reply and explanation. I tried your proposed code and it returned null. When I tried to debugged, I found that mysubset does not return the table. It returns null.

      Moreover, it throws an error on the last statement i.e. COUNTROWS(mylist). Error says "Parameter is not of correct type"

       

  • Icey's avatar
    Icey
    Community Support
    4 years ago

    Hi Dunner2020 ,

     

     

    I want to create a measure that counts individuals having active practice certificates (i.e. not expired) and displays the measure in a line graph where the x-axis contains the fiscal years (i.e. from FY21 to FY22). So I wanted to show the total no of individuals holding active practice certificates each year.

    Authorisation contains information of multiple practicing certificates (i.e. active and expired) of an individual. I want to show the total count of individuals holding active practicing certificates in previous and current fiscal years. For example, for FY17 the logic of picking up holding active practice certificate should be that effective date should be less than or equal to 30-6-2016 and expiry date should be greater than or equal to 1-07-2016

     

    Based on your description, for FY15, the filter conditions should be:

    1. effective date <= 30-6-2014 = MIN ( Dates[Date] ) - 1  => effective date < MIN ( Dates[Date] )
    2. expiry date >= 1-07-2014 = MIN ( Dates[Date] )  => expiry date >= MIN ( Dates[Date] )

     

    So, create your measure like so:

    Active Practice Certificates - v3 = 
CALCULATE (
    COUNTROWS ( Authorisation ),
    FILTER (
        Authorisation,
        Authorisation[Authorisation_Effective_Date__c] < MIN ( Dates[Date] )
            && Authorisation[Authorisation_Expiry_Date__c] >= MIN ( Dates[Date] )
            && Authorisation[Authorisation_Status__c] = "Authorisations Issued"
    )
)

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.