cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rodrigomo
Frequent Visitor

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

Sem título.png

1 ACCEPTED SOLUTION
CheenuSing
Community Champion
Community Champion

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 

 

Capture.GIF

 

If this works for you, please accept this as a solution and also give KUDOS.

 

Cheers

 

CheenuSing

     

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

View solution in original post

2 REPLIES 2
CheenuSing
Community Champion
Community Champion

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 

 

Capture.GIF

 

If this works for you, please accept this as a solution and also give KUDOS.

 

Cheers

 

CheenuSing

     

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

View solution in original post

Thanks @CheenuSing !

Its works very well!

My model is more simple and it allows more features.

The secret is the USERELATIONSHIP() function.

 

Best!

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors