Forum Discussion
Create Table with distinct values and count them
Hello,
I've started working with Power BI desktop, and trying to count how many items I have in a period(it's dynamic)
This is my source table product A:
| Serial# | Manufacturing date | Sale Date |
| 27827 | 9/1/2020 | 10/8/2020 |
| 26645 | 9/23/2019 | 10/8/2020 |
| 25076 | 10/28/2018 | 5/6/2016 |
| 7477 | 11/3/2010 | 5/6/2016 |
| 17484 | 3/2/2015 | 4/7/2015 |
| 28535 | 1/17/2021 | 1/23/2021 |
I'm thinking of creating another table that contains a unique sales date table with distinct sale dates (I've managed to do that)
but now I need to count how many items were sold for each sale date, and here couldn't find a solution.
The result table will look like this:
| Sale Date | # Of items sold |
| 10/8/2020 | 2 |
| 5/6/2016 | 2 |
| 4/7/2015 | 1 |
| 1/23/2021 | 1 |
your help will be much appreciated.
Create an inactive relationship from your Date table to the Sale Date column and then you can use USERELATIONSHIP, like
Num sales = CALCULATE( COUNTROWS('Table'), USERELATIONSHIP( 'Date'[Date], 'Table'[Sale date]))
3 Replies
Create a proper Date table, and link the Date column to the Sale Date column in your current table. You can then use columns from the Date table in any visuals and create a measure like COUNTROWS('Products')
Thanks johnt75, I've created a proper calendar table, and already have a relationship with my product A table (to the manufacturing date), and whenever I try to work with standard measures it doesn't count the relevant rows.
i'm trying to get to some sort of a formula similar to count if Date=Sale Date
Create an inactive relationship from your Date table to the Sale Date column and then you can use USERELATIONSHIP, like
Num sales = CALCULATE( COUNTROWS('Table'), USERELATIONSHIP( 'Date'[Date], 'Table'[Sale date]))