cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Display each day only once

Hello,

I'm using a matrix display with workweek (weeknum.weekday) as columns using the equation

WW = FORMAT(WEEKNUM([timestamp],2),"General Number") & "." & WEEKDAY([timestamp],2)

If there are multiple entries in timestamp that are the same day, I get duplicate workweek columns.

Is there a way to display only once (the latest in the same day)?

Thanks.

12 REPLIES 12
parry2k
Super User
Super User

@Anonymous can you share sample data and what you expect the result to be. read this post to get your answer quickly.

 

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Anonymous
Not applicable

Hi @parry2k 

Here's a sample from the timestamp table

timestamp.pngimage.png

In this case, there are 2 entries in the database from May the third (which gets transformed to week 18 day 5 using the dax equation mentioned previously.  On the table, there is now a second column labeled 18.5 - I'd like there to only be one - each column represents one day, and there should not be duplicates if the timestamp columns has multiple entries for that particular day.

@Anonymous i don't expect to see two columns.

 

so you are using matrix visual, and putting this week column on columns, what is your measure?






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Anonymous
Not applicable

The measure are values aggregated for each day, and also have a timestamp column to indicate when the data was uploaded.   The table used to generate the workweek just has a single timestamp column recording when each data upload happened.

@Anonymous something I'm missing here,please share sample data and if there are more than one tale shre relationship diagram, and also what measure you are using in matrix/






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





@Anonymous works for me, just added ww and new measure you provided earlier.

 

 

image.png

 

 

image.png






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Anonymous
Not applicable

That's really strange.   Under visual level filters for WW, it shows that there's only a single WW 18.5 as well, but it still subbornly displays a duplicate WW 18.5 value

image.png

@Anonymous yes, that's why i'm having hard time understanding why it is showing duplicate, try new matrix visual, or open/close file, or just start new file, very hard to pin point what is the actual issue.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Anonymous
Not applicable

Not sure if it makes a difference, but the measures are pulled from different tables.  They're all in the same format as the one with cb and cs I sent previously, and display summations of values from the exact same timestamp.   All the timestamps from different tables are identical though - data from different tables are all uploaded with identical timestamps recorded.

@Anonymous well without knowing how those table are related and what these measures are, hard to tell. seems like i'm not seeing the full picture. may be is possible send pbix file wiht sample data, keep tables the way it is , otherwise we are just going in circles.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Anonymous
Not applicable

Maybe there is an easier way - is there a way to filter the date column in Powerbi, so that there is only 1 entry per day, and all other entries on the same day is removed/hidden?

Anonymous
Not applicable

Here are some sample timestamps that are used to generate the workweek columns:

timestamp
2019-04-02 11:50:54 AM
2019-04-04 11:51:11 AM
2019-04-05 1:54:26 PM
2019-04-09 4:36:07 PM
2019-04-17 4:42:15 PM
2019-04-23 3:40:11 PM
2019-05-01 4:41:51 PM
2019-05-02 1:50:22 PM
2019-05-03 3:35:03 PM
2019-05-03 4:16:43 PM

 

WW = FORMAT(WEEKNUM([timestamp],2),"General Number") & "." & WEEKDAY([timestamp],2)

 

 

Here's some sample data for 2 of the metrics where cs and cb are being displayed in the matrix, for the dates WW18.5.

The actual display is actually a summation of all the cs/cb values that have the exact same timestamp:

CALCULATE(SUM([cb]), FILTER('table','table'[timestamp]=MAX('table'[timestamp])))

 

timestampcscb
2019-05-03 16:16747272
2019-05-03 16:1611
2019-05-03 16:1611
2019-05-03 16:1681
2019-05-03 16:1661
2019-05-03 16:1622
2019-05-03 16:1622
2019-05-03 16:1622
2019-05-03 16:1622
2019-05-03 16:1652
2019-05-03 16:16382
2019-05-03 15:3511
2019-05-03 15:3581
2019-05-03 15:3561
2019-05-03 15:3522
2019-05-03 15:3522
2019-05-03 15:3522
2019-05-03 15:3522
2019-05-03 15:3552
2019-05-03 15:35382
2019-05-03 15:3542
2019-05-03 15:3542
2019-05-03 15:35122
2019-05-03 15:3562
2019-05-03 15:3512
2019-05-03 15:3533
2019-05-03 15:3573
2019-05-03 15:35103
2019-05-03 15:3543
2019-05-03 15:35103

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors