cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EliskaRychetska
Frequent Visitor

Count with only one row with specific value (start and end date problem)

Hello, 

I have a table which looks like this.

EliskaRychetska_1-1632926303725.png

But I need powerBI to count with just one row for each country and I cant delete the other rows, because they all have different dates.

I got here by having end and start date for each country and I wanted users to be able to choose specific date and let powerBI to show the data that a the specific date falls into. I therefore created a list which entails those dates between, but then when I expand (for the filtering purpose) powerBI sums up the news values as well and the whole number doesnt add up.

 

Maybe there is some other way how to approach this whole start and end date problem, but I couldnt figure out anything.

 

Thank you

 

1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
Community Support

Hi, @EliskaRychetska ;

You could create a caldenar table as a slicer ,then create a measure to calculate the sum or discount.

1.create a calendar table.

Date = CALENDAR(MIN('Table1'[StartDate]),MAX('Table1'[EndDate]))

2.create a sum measure.

sum = CALCULATE(SUM([Value]),FILTER(ALLEXCEPT(Table1,Table1[ID]),[EndDate]>=MIN('Date'[Date])&&[StartDate]<=MAX('Date'[Date])))

The final output is shown below:

vyalanwumsft_0-1633336720482.pngvyalanwumsft_1-1633336744760.png

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
v-yalanwu-msft
Community Support
Community Support

Hi, @EliskaRychetska ;

You could create a caldenar table as a slicer ,then create a measure to calculate the sum or discount.

1.create a calendar table.

Date = CALENDAR(MIN('Table1'[StartDate]),MAX('Table1'[EndDate]))

2.create a sum measure.

sum = CALCULATE(SUM([Value]),FILTER(ALLEXCEPT(Table1,Table1[ID]),[EndDate]>=MIN('Date'[Date])&&[StartDate]<=MAX('Date'[Date])))

The final output is shown below:

vyalanwumsft_0-1633336720482.pngvyalanwumsft_1-1633336744760.png

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

mattww
Super User
Super User

I'm not sure if I'm understanding your problem fully, but it seems like adding an index grouped by country might help, then you can filter on that to only include where the index = 1 to pull out one record per country. Similar to a ROW_NUMBER() with PARTITION in SQL. 

 

See this post which explains how you could go about adding this in Power Query

 

Create Row Number for Each Group in Power BI using Power Query - RADACAD

I managed to do the index, but my main problem is still not working. I will try to explain it.

 

I have a table of campaigns for each country with number of sent emails. These campaigns have a start and an end date. I want to be able to filter in report based on dates in that sense that powerBI shows all the data that falls into the data range. So far I have created column with lists with the dates between the start and end date. I also have created seperate date table (since I have multiple tables from other databases), which I use as a slicer in the report. So now I dont know how to connect those dates between the start and end date (so far they are in the list version and if I expand them, then the issue with more than just the original 1 value for each country appears). 
I tried to make relationship with the special date table and the column with lists from the campaign table, but that seems not to working.

I think I get what you mean, so for example you want a slicer where you select 30 September 2021 and that should be returning data for any campaign which was active at that point, for example a campaign with a Start Date 15 September & End Date 1 October

 

Let me know if I've misunderstood

Yes exactly that.

Got you, so I would have gone with your approach too, expanding your table to create a row for every day between the StartDate and EndDate, then using the new date column for your slicer

 

I would think you can use a MAX / MIN aggregation to then get values out like clicks/opens/etc, as long as you're still grouping by your campaign details, it should work fine, see simplified example below where the top table is the expanded source data, and the bottom table shows how you'd essentially deduplicate this using aggregation

If you just want to count the number of campaigns, use DISTINCTCOUNT on something like a campaign id column.

 

mattww_0-1633087023571.png

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

 

Matt

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!