Count Number of Active Modifications

[qbarnes]

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.

[TomMartens]

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]

@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]

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

[qbarnes]

I appreciate your time.   The # Active MOD column signifies the actual result i was looking for, as result of the DAX formula.  

The two items I want to consider iswhen the ModType is a "Payment Holiday" and if the "ToDate" is still blank. 

• I removed this portion from the DAX:  , KEEPFILTERS([Active # MODs] = 1)

However, the result doesnt produce any values.  

This is the actual formulat I've been trying before creating this post:  

Total PMT Holiday MODs =

calculate(DISTINCTCOUNT(Modifications[AcctID]),

Search("Payment Holiday", Modifications[ModType],,0)

, KEEPFILTERS((Modifications[ToDate] = ""

)))

[v-yiruan-msft · Accepted solution]

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

[TomMartens]

Hey @qbarnes ,

the line

KEEPFILTERS((Modifications[ToDate] = "")

is not the same as this line

ISBLANK(Modifications[ToDate])

ISBLANK filters for null whereas ... = "" filters for an empty string.

Please consider to create a PBIX file that contains sample data, upload the pbix to onedrive or dropbox, and share the link.

Regards,

Tom