Forum Discussion

qbarnes's avatar
qbarnes
Icon for Helper I rankHelper I
6 years ago
Solved

Count Number of Active Modifications

Hello Everyone, 

 

I'm seeking a DAX solution that can kick back the # of Active Modifications by a specific Modification Type, that includes the phrase "Payment Holiday"...

 

The Modifications Tables I'm using has a Column called 'ModType', which is where "Payment Holiday" can be found.  There's also seperate Columns for "From Date" and "To Date" (which are date values).

 

ModType               |  From Date   |  To Date       |  Active # MOD

Payment Holiday   |  3/20/2020   |                      |  1 

Payment Holiday   |  2/12/2020   |   4/12/2020  |  0 

 

What I'm having trouble with is counting the ACTIVE number of "Payment Holidays" by counting the 'blanks values' in the "To Date" field... 

  • The blanks values in the To Date field signifies that the modification has not ended. 
    • I want to be able to pull the sum of the resulting output. 

 

I've tried a number of different things, but the error im receiving is based PBI's inability to comparing a Date Value with a Text Value.  I'm stuck, and would definately appreciate any and all assistance with this.

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi qbarnes ,

    It seems there are 2 problems in field ModType(some spaces in the text value) and ToDate(blank values), so please deal with them in Power Query Editor first.

    1. Remove the spaces in field ModType

     #"Trimmed Text" = Table.TransformColumns(Source,{{"ModType", Text.Trim, type text}})

    Trim spaces

    2. Replace the "blank" values of field ToDate with ""

    #"Replaced Value"= Table.ReplaceValue(#"Trimmed Text","#(00A0) #(00A0) #(00A0) #(00A0) #(00A0) #(00A0) #(00A0) #(00A0) #(00A0) #(00A0) #(00A0) ","",Replacer.ReplaceText,{"To Date"})

    Replace values

    Then create a measure as below to get the number of Active Modifications:

    Total PMT Holiday MODs =
CALCULATE (
    DISTINCTCOUNT ( 'Modifications'[AcctID] ),
    FILTER (
        'Modifications',
        Modifications[ModType] = "Payment Holiday"
            && ISBLANK ( 'Modifications'[To Date] )
    )
)

    Best Regards

    Rena

6 Replies

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    6 years ago

    Hey qbarnes ,

     

    this is the measure I created:

     

    count empty To Date = 
CALCULATE(
    COUNTROWS('Table')
    , ISBLANK('Table'[To Date])
    , KEEPFILTERS('Table'[ModType] = "Payment Holiday")
    , KEEPFILTERS('Table'[Active # Mod] = 1)
)

     

     

     

    This is how my table looks like:

    And here is a little card visual using that measure:



    Hopefully, this provides what you are looking for, if not, please consider creating a pbix file that contains sample data, but still reflects your data model. Upload the file to onedrive or dropbox and share the link.

     

    Regards,

    Tom

    • qbarnes's avatar
      qbarnes
      Icon for Helper I rankHelper I
      6 years ago

      TomMartens, 

       

      Thanks for the quick response.   This is great the only variable is that the ModType column has several types of modifications, and I want to count the active mods by the category "Payment Holiday"

      • TomMartens's avatar
        TomMartens
        Icon for Super User rankSuper User
        6 years ago

        Hey qbarnes ,

        I edited the DAX in my initial post. Now, only the ModType = "Payment Holiday" and Active # MOD = 1 is considered for the counting.

         

        Regards,

        Tom