topic Re: Assign value from lookup table when no data is present in DAX Commands and Tips

Assign value from lookup table when no data is present

sdgiss — Wed, 17 Mar 2021 18:23:23 GMT

Hello PBI Community!

I have what I think is a simple question but as of yet have not been able to produce the appropriate outcome.

I have a date table, a fact table, and a lookup table. The lookup table has daily standards (goals) that I will be using to compare to the actual daily output. On the days when I don't have data for an individual it does not report the goal for that day (Individual 4, below). As you would guess, I would like to report the goal even if they don't have data.

Any guidance would be much appreciated!

Steve

Daily report dTrainMatch

Goal Dist =
VAR sumgoaldist =
    SUMX (
        fDataReference,
        LOOKUPVALUE (
            dTrainMatch[Goal-Dist],
            dTrainMatch[Date], RELATED ( dDateSeason[Date] )
        )
    )
RETURN
    IF (
        [Total Dist] = BLANK (),
        sumgoaldist * [Top 10 Avg Dist],
        sumgoaldist * [Top 10 Avg Dist]
    )

Re: Assign value from lookup table when no data is present

Anonymous — Thu, 18 Mar 2021 15:20:23 GMT

The IF in the measure is totally useless. Since both expressions return the same thing, what does it do?

Re: Assign value from lookup table when no data is present

sdgiss — Thu, 18 Mar 2021 18:13:40 GMT

Thank you for the response, Anonymous,. When you're as new as I am to DAX bad code means that one is desperate to find a solution and will make silly mistakes along the way. I am just trying to find a solution to my problem.

Re: Assign value from lookup table when no data is present

Anonymous — Thu, 18 Mar 2021 18:40:16 GMT

sdgiss

Before trying to write anything... everyone who is new to DAX should read this: Understand star schema and the importance for Power BI - Power BI | Microsoft Docs

After that, it should be easier for you to understand where you go wrong... Please always remember that "not everything that can be done should be done."

Re: Assign value from lookup table when no data is present

sdgiss — Thu, 18 Mar 2021 18:56:46 GMT

Thank you for sending along this reference Anonymous. I've read it before, but I think it would be worth reading it again.

Also, your saying fits my situation. I ended up finding a solution to my problem and it had little resemblence to what I posted above. The IF statement has been removed.