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.
I have two tables
Table 1
Admins Table | |
Processingadmin_ID | Admin_Name |
1 | Admin1 |
2 | Admin2 |
3 | Admin3 |
Table 2: Loan Table with Created date of last two months: April and May
Loans Table | ||||
LeadID | Lead_Created_Date | Funding_Amount | Approve_Date | Processingadmin_ID |
32 | 01/05/21 | 5000 | 05/05/21 | 1 |
33 | 02/05/21 | 6000 | 06/05/21 | 2 |
34 | 03/04/21 | 0 | 03/04/21 | 3 |
35 | 04/04/21 | 7000 | 04/05/21 | 1 |
36 | 05/05/21 | 5000 | 05/05/21 | 2 |
37 | 06/05/21 | 2000 | 08/05/21 | 3 |
38 | 07/05/21 | 3 |
I want output in following format
Required Output | |||
Admin_Name | Approved Loans Count | Leads Count | New Loan Count |
Admin1 | 2 | 1 | 0 |
Admin2 | 2 | 2 | 1 |
Admin3 | 0 | 1 | 0 |
Logics to be used:
From | To | |||
Selected Date (Slicer) | Using | Lead_Created_Date | 01/05/21 | 07/05/21 |
Approved Loans Count Logic | Count of LeadID from Loans Table if Funding_Amount>0 and Approve_Date is not blank and Approve_Date is between date selected in slicer |
Leads Count Logic | Count of LeadID from Loans Table if Lead_Created_Date is between date selected in slicer |
New Loan Count | Count of LeadID from Loans Table if Funding_Amount>0 and Approve_Date is not blank and Approve_Date is between date selected in slicer and Lead_Created_Date=Approve_Date |
Approved Loan Count should be basis Approved date(Approved date should be between date range selected in slicer)
Solved! Go to Solution.
The numbers in Approved Loans Count and Count Leads Count do not follow from the description of the calculations and the initial conditions. You should make the description 1) absolutely clear and 2) show correct numbers because currently there's mistakes/contradictions in the calculations.
DEFINE
MEASURE Measures_[Lead Count] =
// Count of LeadID from Loans Table
// if Lead_Created_Date is between
// date selected in slicer. This condition
// is automatically satisfied due to the
// relationship between Loans and Created Dates.
DISTINCTCOUNT( Loans[LeadID] )
MEASURE Measures_[New Loan Count] =
// Your description:
// Count of LeadID from Loans Table
// if Funding_Amount > 0 and Approve_Date
// is not blank and Approve_Date is
// between date selected in slicer
// and Lead_Created_Date = Approve_Date
CALCULATE(
DISTINCTCOUNT( Loans[LeadID] ),
KEEPFILTERS( Loans[Funding Amount] > 0 ),
FILTER(
SUMMARIZE(
Loans,
Loans[Approval Date],
Loans[Lead Created]
),
Loans[Approval Date] = Loans[Lead Created]
)
)
MEASURE Measures_[Approved Loan Count] =
// Your description:
// Count of LeadID from Loans Table
// if Funding_Amount > 0 and Approve_Date
// is not blank and Approve_Date is between
// date selected in slicer.
CALCULATE(
COUNTROWS( Loans ),
KEEPFILTERS(
TREATAS(
DISTINCT( 'Created Dates'[Created Date] ),
Loans[Approval Date]
)
),
KEEPFILTERS( Loans[Funding Amount] > 0 )
)
DEFINE
MEASURE Measures_[Lead Count] =
// Count of LeadID from Loans Table
// if Lead_Created_Date is between
// date selected in slicer. This condition
// is automatically satisfied due to the
// relationship between Loans and Created Dates.
DISTINCTCOUNT( Loans[LeadID] )
MEASURE Measures_[New Loan Count] =
// Your description:
// Count of LeadID from Loans Table
// if Funding_Amount > 0 and Approve_Date
// is not blank and Approve_Date is
// between date selected in slicer
// and Lead_Created_Date = Approve_Date
CALCULATE(
DISTINCTCOUNT( Loans[LeadID] ),
KEEPFILTERS( Loans[Funding Amount] > 0 ),
FILTER(
SUMMARIZE(
Loans,
Loans[Approval Date],
Loans[Lead Created]
),
Loans[Approval Date] = Loans[Lead Created]
)
)
MEASURE Measures_[Approved Loan Count] =
// Your description:
// Count of LeadID from Loans Table
// if Funding_Amount > 0 and Approve_Date
// is not blank and Approve_Date is between
// date selected in slicer.
CALCULATE(
COUNTROWS( Loans ),
KEEPFILTERS(
TREATAS(
DISTINCT( 'Created Dates'[Created Date] ),
Loans[Approval Date]
)
),
KEEPFILTERS( Loans[Funding Amount] > 0 )
)
The numbers in Approved Loans Count and Count Leads Count do not follow from the description of the calculations and the initial conditions. You should make the description 1) absolutely clear and 2) show correct numbers because currently there's mistakes/contradictions in the calculations.
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 |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
42 | |
32 | |
30 | |
18 | |
18 |