Forum Discussion
Consolidated Date table
Hello,
I have an 'Archive' table where several entries, including a yes/no column, are made every day. I have tried making a table an usings SELECTCOLUMNS, but I still end up with the same number of date rows because they all have times attached.
**How can I create a table to show the date and the count of Yesses and Nos for each date?
My goals:
- Create a table showing the count of Yes and No for each date, and the number of yes as a percentage of the total entries for the date
- create a dashboard showing MTD, and YTD percentages, both in the form of a Matrix/table as well as line graphs.
With help with my **question, I am confident I can get the rest sorted out.
Hi Samuelal ,
I have created a simple sample, please refer to it to see if it helps you.
Create a measure.
count_measure = CALCULATE ( COUNT ( 'Table'[received in order] ), FILTER ( ALL ( 'Table' ), 'Table'[received in order] = SELECTEDVALUE ( 'Table'[received in order] ) && 'Table'[created] = SELECTEDVALUE ( 'Table'[created] ) ) )If I have misunderstood your meaning, please provide more details with your desired output.
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
*Basically I want to make a pivot table to organize my information from which I can make my visuals.
- parry2k
Super User
Samuelal does yes/no value in a one-column or this value is in two seperate columns? The solution will be different based on how your data looks like. Post sample data here to get the exact solution.
✨ Follow us on LinkedIn and to our YouTube channel
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.
The yes/no values are in a single column. Below is a screen shot of a portion of data. In this screen shot all of the items are from the same date and time, but items can be made throughout the day.
Hi Samuelal ,
I have created a simple sample, please refer to it to see if it helps you.
Create a measure.
count_measure = CALCULATE ( COUNT ( 'Table'[received in order] ), FILTER ( ALL ( 'Table' ), 'Table'[received in order] = SELECTEDVALUE ( 'Table'[received in order] ) && 'Table'[created] = SELECTEDVALUE ( 'Table'[created] ) ) )If I have misunderstood your meaning, please provide more details with your desired output.
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- parry2k
Samuelal first you need to add a date dimension in your model to work with time intelligence functions. you can check the playlist on time intelligence on my YT channel here. https://youtube.com/playlist?list=PLiYSIjh4cEx2FkuGkngYruS0wm8MYFsoi
Once you have the date dimension, in a matrix visual add a date from the date dimension on the row, received on orders on the column, and the count of rows on values and that will do it.
First get to this part and from here everything will be easy.
✨ Follow us on LinkedIn and to our YouTube channel
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.
