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.
= Sum(Fields.ADDTL_INST + Fields.ADDTL_REF + Fields.EXCISE_TAX + Fields.NON_STANDARD_FEES + Fields.RECORDING_FEES + Fields.EXC_ENT + Fields.UCC_FEES + Fields.UAP_FEES) - Sum(Fields.UCC_FEES) - Sum(Fields.EXCISE_TAX) - Sum(Fields.NON_STANDARD_FEES) - SUM(iif((Fields.ADDTL_INST > 0 Or Fields.ADDTL_REF > 0 Or Fields.EXCISE_TAX > 0 Or Fields.RECORDING_FEES > 0 Or Fields.NON_STANDARD_FEES > 0) AND Fields.DT_DESC = "D - T", (Fields.ADDTL_INST + Fields.ADDTL_REF + Fields.EXCISE_TAX + Fields.NON_STANDARD_FEES + Fields.RECORDING_FEES + Fields.EXC_ENT), 0.00)) - COUNT(iif((Fields.ADDTL_INST > 0 Or Fields.ADDTL_REF > 0 Or Fields.EXCISE_TAX > 0 Or Fields.RECORDING_FEES > 0 Or Fields.NON_STANDARD_FEES > 0 Or Fields.UCC_FEES > 0) AND (Fields.DT_DESC = "DEED" OR Fields.DT_DESC = "EASMT" OR Fields.DT_DESC = "SEE INSTRUMENT"), 1, null)) * 6.2
Your original query was using a lot of OR statements. Try replacing statements like:
I would split the sub CALCULATATES into their own measures, the write the Fee measure to add the sub measures together.
Also, in the first part of the query it looks like three of the fields you are adding together you are taking back out right afterwards.
Can you just leave those 3 out of the first sum?
For the last 6 steps, the ones that multipl a count by 6.2, looking at the sql I think you can just do that this way.
6.2 measure =
CALCULATE(
COUNTROWS(Query1),
KEEPFILTERS(Query1[DT_DESC] IN {"DEED","EASMT","SEE INSTRUMENT"}),
(Query1[ADDTL_INST] > 0 || Query1[ADDTL_REF] > 0 || Query1[EXCISE_TAX] > 0 || Query1[RECORDING_FEES] > 0 || Query1[NON_STANDARD_FEES] > 0 || Query1[UCC_FEES] > 0)
) * 6.2
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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |