Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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
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 @CheenuSing !
Its works very well!
My model is more simple and it allows more features.
The secret is the USERELATIONSHIP() function.
Best!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |