Eliminating null or duplicate rows in a table calculated from summarize function

[shelbsassy]

I created a table to get the distinct irst date of the month and the Max Service Date (from the data load) from 

TrendCalcs = SUMMARIZE(Dates,Dates[FirstDateC],Dates[MaxPerMonth]) and I added a column to show the number of days that represents as NoDays

which gives me  this

but I can't create a relationship between FirstDateC and either of the other tables because it is saying there must be unique values in one of the tables.  If I remove the null values from this table it will be unique FirstDateC but in creating the tbale with the sumamrize function the table does not present itself in the query editor to try and remove those top 3 rows.  Anyone know how I can eliminate all but the bottom 2 rows?  Thanks so much!

[Anonymous]

Had the same issue in a similar context.

Approvers = CALCULATETABLE(ALLNOBLANKROW(Persons[Name];Persons[ID]))

seems to work in removing blank rows

[Phil_Seamark · Accepted solution]

Hi @shelbsassy

You could try something like this?

TrendCalcs = SUMMARIZE(
        FILTER(
            Dates,
            'Dates'[FirstDateC]<> BLANK()
            ),
            'Dates'[FirstDateC],
            Dates[MaxPerMonth]
            )

[shelbsassy]

NOt sure if you can help me with the next part of this problem but I have the calculated ciolumn called MOnthDays which is 

MonthDays = VAR LastDayThisMonth = CALCULATE(MAX(Dates[Date]), ALLEXCEPT(Dates,Dates[YearMonth])) RETURN (DAY(LastDayThisMonth))

to give me the number if days in a month.  Basically I want to divide that NoDays from the TrendCalcs table by the maximum number of days in that month.  Everytime I try to create a measure or a column I am either not getting the field I need in the intellisense dropdown or when I try to do a sum(dates([monthdays]/sum(trendcalcs[NoDays]) I am getting a calculation for everythign in that column but I only want the distinct value for that month.  The result is 209.64.  

For January since there are 31 days in the month and the max date of data is 31 then the value should be 1 when 31/31.  For February theres 28 days and the max date of data is 22 so the value should be .7857.

Any thoughts or insight?  Thank you!

[shelbsassy]

OMG Worked beautifully the first try!  Thank you so much!