Forum Discussion
Create measures grouping by two columns with the same values and using filters
Hi folks,
I'm new in Power Bi but I have worked with QlikView.
Now, I'm trying to developer a desktop solution that requires create two count measures based in two different columns with the same value.
In pratice, I have a table with my sales records that contains the sale_date an the sale_deluveyr. Based on it, i need construct a summary table with the distinct dates on the rows, one column with the count of sales happened and another column with the count of sales delivered. In addition this, I need to maintain filters from another column of my original table.
Above an image of what I need.
Can you help me with this?
Thanks
Hi rodrigomo
Try the following steps
1. Ensure your salesdate and deliverydate are of the type ( DD/MM/YYYY)
2. Create a calendar table
Calendar = Calendar(Min(SalesData[SalesDate]),MAX(SalesData[SalesDate]))
3. This will create a calendar table with Date as a column
ENsure the Date column is also of the type (DD/MM/YYYY)
4. Using Manage Relationship
Create realtionship between SalesData[SalesDate] and Calendar[Date]
Caridnality should be many to one and cross filter direction - Both
Check Make this as Active relationship
5. Similarly create a relationship between Sales[DeliveryDate] and Calendar[Date]
Caridnality should be many to one and cross filter direction - Both
UnCheck Make this as Active relationship
6. Create the measures
a) SalesDone = SalesDone = Distinctcount(SalesData[SalesId])+0
Adding 0 for days when there was no Sales on that day
b) DeliveryDone =
Calculate (Distinctcount(SalesData[SalesId])+0,USERELATIONSHIP(SalesData[DeliveredDate],'Calendar'[Date]))
This time around we are telling DAX to userelationship between DeliveredDate and CalendarDate which is not
active.
c) Create measure SalescumDelivery = [SalesDone] + [DeliveryDone]
7. Create a table chart , picking values
a) Calendar[Date]
b) [SalesDone] measure
c) [DeliveryDone] measure
d) Under the Filters for the table drag the SalescumDelivery and apply the condition is not 0
8. Create a slicer for SalesCategory.
9. Check everything works
10. A sample screen shot
If this works for you, please accept this as a solution and also give KUDOS.
Cheers
CheenuSing
2 Replies
Hi rodrigomo
Try the following steps
1. Ensure your salesdate and deliverydate are of the type ( DD/MM/YYYY)
2. Create a calendar table
Calendar = Calendar(Min(SalesData[SalesDate]),MAX(SalesData[SalesDate]))
3. This will create a calendar table with Date as a column
ENsure the Date column is also of the type (DD/MM/YYYY)
4. Using Manage Relationship
Create realtionship between SalesData[SalesDate] and Calendar[Date]
Caridnality should be many to one and cross filter direction - Both
Check Make this as Active relationship
5. Similarly create a relationship between Sales[DeliveryDate] and Calendar[Date]
Caridnality should be many to one and cross filter direction - Both
UnCheck Make this as Active relationship
6. Create the measures
a) SalesDone = SalesDone = Distinctcount(SalesData[SalesId])+0
Adding 0 for days when there was no Sales on that day
b) DeliveryDone =
Calculate (Distinctcount(SalesData[SalesId])+0,USERELATIONSHIP(SalesData[DeliveredDate],'Calendar'[Date]))
This time around we are telling DAX to userelationship between DeliveredDate and CalendarDate which is not
active.
c) Create measure SalescumDelivery = [SalesDone] + [DeliveryDone]
7. Create a table chart , picking values
a) Calendar[Date]
b) [SalesDone] measure
c) [DeliveryDone] measure
d) Under the Filters for the table drag the SalescumDelivery and apply the condition is not 0
8. Create a slicer for SalesCategory.
9. Check everything works
10. A sample screen shot
If this works for you, please accept this as a solution and also give KUDOS.
Cheers
CheenuSing
Thanks Anonymous !
Its works very well!
My model is more simple and it allows more features.
The secret is the USERELATIONSHIP() function.
Best!
