Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
PBI_newuser
Post Prodigy
Post Prodigy

Distinct Count of Product with multiple criteria

Hi, I wanted to count the product based on the type defined below:

  • Rental: Count the product if it has purchased any contract with contract term Rental. E.g. Product C has active "Rental" contract in 2022-02 and 2022-03. Product B has active "Warranty" in 2022-02 and hence the "Rental" contract shouldn't count here. Product B should be included in the count in 2022-03.
  • Contract: Count the product if there is any "Purchased" contract active in the month. E.g. Product H has active "Purchased" contract in 2022-02 and Product A has active "Purchased" contract in 2022-03.
  • Warranty: Count the product if there is active "Warranty" in the month. E.g. Product A has active "Warranty" in 2022-02 and has active "Purchased" contract in 2022-03. Same goes to B,D,G.
  • T&M: Count the product if they don't have contract or expired contracts. E.g. Product E has installed after 2021-02 and has no contract, it's considered T&M. Product F has expired contract in 2022-02. Product H has expired contract in 2022-03 but active contract in 2022-02. Product I is installed in 2022-03 and it has no contract.

Each product should be counted as one in each month. How to create such measure?

 

ProductTermContract Start DateContract End DateInstalled Date
APurchased02/13/202201/12/202301/10/2019
AWarranty1/13/202102/12/202201/10/2019
BRental02/23/202202/22/202605/11/2009
BWarranty02/23/202102/22/202205/11/2009
CRental05/01/202103/28/202205/09/2019
CRental09/29/202004/30/202105/09/2019
DWarranty3/31/202103/30/202205/09/2019
DPurchased3/31/202203/30/202605/09/2019
E   02/13/2021
FPurchased01/13/202101/12/202201/10/2019
FWarranty1/13/202001/12/202101/10/2019
GWarranty01/13/202103/12/202201/10/2019
HPurchased12/13/202002/12/202201/10/2019
I   03/13/2022

 

Expected Output:

Type2022-022022-03
Rental1 (C)2 (B,C)
Contract1 (H)1 (A)
Warranty4 (A,B,D,G)2 (D,G)
T&M2 (E,F)4 (E,F,H,I)

 

Sample pbix here. If a product is valid for both "Contract" & Warranty" in the same period, it should count only "Warranty" for that product. If a product is valid for both "Rental" & "Warranty" in the same month, it should count only "Warranty" for that product. How to do so? 

 

The sum for T&M is incorrect? How to fix it?

PBI_newuser_1-1649059592334.png

2 REPLIES 2
amitchandak
Super User
Super User

@PBI_newuser , I think this approach of HT blog with active inactive join should help. What you need is very similar to Active employees

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Hi @amitchandak , thank you for your suggestion.

I have created some measures in this sample pbix file. If a product is valid for both "Contract" & Warranty" in the same period, how to count only "Warranty" for that product. (E.g. Product A in 2022-02 and Product D in 2022-03) If a product is valid for both "Rental" & "Warranty" in the same month, how to count only "Warranty" for that product. (E.g. Product B in 2022-02)

 

Besides, the sum for T&M is incorrect? How to fix it?

PBI_newuser_0-1649059955471.png

 

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.