Dynamically doing lookup and getting sum of the column

[AshishKumarS]

Book.xlsx

What needs to be achieved.xlsx

Hi All,

I have a data where the requirement is as follows:

When the customer 12345 is selected from the slicer and Year and Month is selected example :Year-2024 and Month 10 then below things has to be done in the measure.

Get the data from the table1 where the flag will be IT and Year will be 2024 and Month will be 9.

Another table2  where the flag will be IPT and Year will be 2024 and Month will be 9 and 10.

Now if we see the excel "What needs to be achieved" A and B column indicates IT and L and M indicates IPT.

Need to do the look up on L and M column for those invoices which are present in A column. and then sum the currency.

All this has to be dynamic. If I select Year -2023 and Month-6 then IT flag will be 5 and IPT will be 5 and 6 month and year will be 2023 for both.

I have attached the sample data in the form of Book.xlsx and What need to be achieved in another excel.

Any help will be highly appreciated . I am stuck in this from 1 month.

[johnbasha33]

@AshishKumarS 

It sounds like you need to create a dynamic measure in Power BI that retrieves data from different tables based on user selections in slicers and performs calculations according to specific conditions. Here's how you can approach this:

1. **Define Parameters:**
- Create parameters in Power BI for Customer, Year, and Month selections. These parameters will drive the dynamic behavior of your measure.

2. **Write DAX Measures:**
- Write DAX measures to retrieve data from the appropriate tables based on the selected parameters.
- Use the `SELECTEDVALUE` function to retrieve the selected values from slicers.
- Use `CALCULATE` to filter data based on conditions.
- Use `LOOKUPVALUE` to perform lookups between tables and retrieve corresponding values.
- Use `SUMX` or `SUMMARIZE` to aggregate the data.

3. **Example DAX Measures:**
- Measure1: Retrieve data from Table1 based on the selected Customer, Year-1, Month-1, and Flag "IT".
- Measure2: Retrieve data from Table2 based on the selected Customer, Year-1, Month-1, and Flag "IPT".
- Measure3: Perform lookup on Table2 using values from Measure1, and calculate the sum of currency based on the lookup result.

4. **Ensure Dynamism:**
- Use DAX functions such as `MAX`, `MIN`, or `LASTDATE` to ensure that the measures dynamically adapt to changes in slicer selections for Year and Month.
- Use DAX logic to dynamically determine the months and years to retrieve data from Table2 based on the selected Month and Year.

5. **Testing and Validation:**
- Test the measures with different slicer selections to ensure they produce the expected results.
- Validate the results against the provided sample data to ensure accuracy.

Here's a simplified example of what the DAX measures might look like:

```DAX
Measure1 =
CALCULATE(
SUM(Table1[Currency]),
FILTER(
Table1,
Table1[Customer] = SELECTEDVALUE(CustomerParameter) &&
Table1[Year] = SELECTEDVALUE(YearParameter) - 1 &&
Table1[Month] = SELECTEDVALUE(MonthParameter) - 1 &&
Table1[Flag] = "IT"
)
)

Measure2 =
CALCULATE(
SUM(Table2[Currency]),
FILTER(
Table2,
Table2[Customer] = SELECTEDVALUE(CustomerParameter) &&
Table2[Year] = SELECTEDVALUE(YearParameter) - 1 &&
(Table2[Month] = SELECTEDVALUE(MonthParameter) - 1 || Table2[Month] = SELECTEDVALUE(MonthParameter)) &&
Table2[Flag] = "IPT"
)
)

Measure3 =
CALCULATE(
SUMX(
FILTER(
Table2,
Table2[Customer] = SELECTEDVALUE(CustomerParameter) &&
(Table2[Flag] = "IPT" && (Table2[Month] = SELECTEDVALUE(MonthParameter) - 1 || Table2[Month] = SELECTEDVALUE(MonthParameter)))
),
IF(
LOOKUPVALUE(Table1[Invoice], Table1[Invoice], Table2[L]) = SELECTEDVALUE(Table2[L]) &&
LOOKUPVALUE(Table1[Invoice], Table1[Invoice], Table2[M]) = SELECTEDVALUE(Table2[M]),
Table2[Currency]
)
)
)
```

These measures are based on the assumption of table and column names. Please adjust them according to your actual data model. Test these measures thoroughly with your data to ensure they meet your requirements.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!