Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
shelbsassy
Resolver I
Resolver I

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

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

 

 

Untitled.pngwhich 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!

1 ACCEPTED SOLUTION
Phil_Seamark
Employee
Employee

Hi @shelbsassy

 

You could try something like this?

 

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

Had the same issue in a similar context.

 

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

 

seems to work in removing blank rows

Phil_Seamark
Employee
Employee

Hi @shelbsassy

 

You could try something like this?

 

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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!

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.