Create measures grouping by two columns with the same values and using filters

[rodrigomo]

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

[Anonymous · Accepted 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

[rodrigomo]

Thanks @Anonymous !

Its works very well!

My model is more simple and it allows more features.

The secret is the USERELATIONSHIP() function.

Best!