Using LookUpValue inside a measure

[mp390988]

Hello,

I have this below:

I want to add a column that shows the targets for each dealer group for each month.
I already created a summarised table as per below that groups the target by dealer and month.

My model looks like this where the fields for my matrix are marked in yellow but I need to bring in the one marked in red.

I can't create a relationship between my RevenueTargetsSummarised table and the CalendarTbl or with the TradesTbl because it results in a many-to-many relationship. Therefore, I created a measure as per below to retrieve the targets.

However, when I drop this into the values section of my matrix visual I get an error:

Is there something wrong in my formula?

Thank You in advance

[v-achippa]

Hi @mp390988,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @DataNinja777 , @FBergamaschi and @Ashish_Mathur for the prompt response.

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @mp390988,

We wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @mp390988,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

[mp390988]

So I managed to get my lookupvalue function working in my measure but my issue now is that I need to somehow display the sum in the yellow marked section.

How can I acheive this in the measure definition.

[FBergamaschi · Accepted solution]

At the total the SELECTEDVALUE call provides a blank value

probably the solution is

SUMX ( 
        SUMMARIZE ( 
                  RevenuesTargetsSummarised,
                  RevenueTargetsSummarised[DealerGroup],
                  CalendarTbl[Month]
        ),

          LOOKUPVALUE(
                 RevenueTargetsSummarised[MonthlyTargets],
                 RevenueTargetsSummarised[DealerGroup], SELECTEDVALUE(TradesTbl[DealerGroup]),
                 RevenueTargetsSummarised[Month], SELECTEDVALUE(CalendarTbl[Month])
          )

)

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[DataNinja777]

Hi @mp390988 ,

Your visual is failing because of a data type mismatch in your LOOKUPVALUE formula. You are attempting to find a match by comparing the RevenueTargetsSummarised[Month] column, which is a date (e.g., 01/01/2025), with the result of MAX(CalendarTbl[MonthYear]), which is text (e.g., "Jan-25"). Since a date and a piece of text will never be equal, the formula cannot find a value and returns an error.

A quick way to fix this is to adjust your DAX measure to retrieve the correct date value from the visual's filter context. Instead of using MAX(CalendarTbl[MonthYear]), you can use MIN(CalendarTbl[Date]), assuming your calendar table has a Date column. This will fetch the first day of the month for the given row (e.g., 01/01/2025 for the "Jan-25" row), which can then be successfully matched to your targets table.

RevTrgtByDealerGrpMonthYear =
LOOKUPVALUE(
    RevenueTargetsSummarised[MonthlyTargets],
    RevenueTargetsSummarised[DealerGroup], SELECTEDVALUE(TradesTbl[DealerGroup]),
    RevenueTargetsSummarised[Month], MIN(CalendarTbl[Date]),
    0
)

However, the recommended and more robust solution is to improve your data model. Creating disconnected summary tables is often inefficient. You should delete the RevenueTargetsSummarised table entirely. Instead, use your original RevenueTargetsTbl and create a one-to-many relationship between your CalendarTbl[Date] column and your RevenueTargetsTbl[Month] column.

Once that relationship is established, you no longer need a complex LOOKUPVALUE function. You can create a much simpler and more efficient measure. This new measure will be automatically filtered correctly by the relationships in your model when you place it in the matrix visual. This approach is the standard best practice in Power BI.

Monthly Targets = SUM(RevenueTargetsTbl[Target])

Best regards,

[mp390988]

Hi @DataNinja777 ,

Thank you for your response.

I prefer to follow your advice on removing the disconnected table because I want to keep my model clean. Therefore, as per your suggestion, I created a relationship between the original RevenueTargetsTbl and the CalendarTbl  and also created a measure called MonthlyTargets, all captured in the snapshot below:

However, I noticed this measure is giving the total taregt irrespective of the dealer group.
For example, when DealerGroup = "Derivatives", the measure returns the following:

When the actual values it should give should be the following:

But when I change the dealer group to say "Phil", I also get the same values:

Or if I change to "UAE" also get the same values:

any idea of whats going on and how to fix please?

thank you

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.