Hi @jmario1987 ,
Using a separate table for managing commission rates is a more efficient and scalable approach compared to hardcoding the logic in a DAX formula. This method improves maintainability, as updates to commission rates or conditions can be made directly in the table without modifying the DAX code. It also makes the logic more transparent and easier to understand for others working on the report. Additionally, this approach ensures that the model remains flexible, allowing for future changes without extensive rework.
To implement this, you can create a separate "Commission Rates" table in Power BI. This table would include columns such as Product Condition, Start Date, End Date, and Commission Rate. For example:
| Product Condition |
Start Date |
End Date |
Commission Rate |
| A |
01/01/2023 |
10/01/2024 |
0.35 |
| B |
01/01/2023 |
10/01/2024 |
0.35 |
| C |
01/01/2023 |
10/01/2024 |
0.35 |
| D |
01/01/2023 |
10/01/2024 |
0.35 |
| E |
01/01/2023 |
10/01/2024 |
0.3 |
| F |
01/01/2023 |
10/01/2024 |
0.3 |
| All Conditions |
10/02/2024 |
12/31/2025 |
0.42 |
This table can be created using Power BI's "Enter Data" feature or imported from an external source like Excel or a database. Once the table is created, you can establish a relationship between this table and your main data table (Datos Mensuales) using the Condición afiliación column. Optionally, you can also use Fecha Reporte for additional filtering.
The DAX measure can then be updated to dynamically retrieve the applicable commission rate from the new table. The revised measure would look like this:
Comision =
SUMX(
'Datos Mensuales',
VAR CurrentDate = 'Datos Mensuales'[Fecha Reporte]
VAR CurrentCondition = 'Datos Mensuales'[Condición afiliación]
VAR CommissionRate =
CALCULATE(
MAX('Commission Rates'[Commission Rate]),
'Commission Rates'[Product Condition] = CurrentCondition &&
CurrentDate >= 'Commission Rates'[Start Date] &&
CurrentDate <= 'Commission Rates'[End Date]
)
RETURN
'Datos Mensuales'[Comisión ADQ] * CommissionRate
)
This measure dynamically calculates the commission by looking up the rate from the "Commission Rates" table based on the Product Condition and the date range. It ensures that the correct rate is applied without requiring hardcoding, making the model more robust and easier to adapt to future changes. By following this approach, your Power BI report will be more modular, scalable, and easier to maintain.
Best regards,
