UrBizEdge Blog: Power BI DAX #3: VALUES, another must know DAX formula

One common situation sales analysts face is capturing newly introduced products or SKUs or Region or Sales Rep in their reports. In Excel, you'll have to do something extra -- and if you are not an Excel ninja, that something extra can be very significant and time costly.

With Power BI, you might not need to do anything extra if you are good with using VALUES.

CASE STUDY
You are the sales analyst for Mega Pizza. Everyday you report sales by branch. But every couple of months, a new branch is opened. On the ERP the sales for each new branch is automatically captured, but on your report you'll have to include this new branch and assign it the general target given to all branches.

How will you achieve this automatically in Power BI without having to do any extra/manual intervention when a new branch is live.

ANSWER: VALUES

First, a screenshot of what the transaction data from the ERP looks like.

We are going to create a new table from this table extracting the unique list of branches from the ERP live data table. And that's exactly what VALUES does.

VALUES is the DAX formula that extracts a unique list of records from a table or column. You can read more about it from Microsoft Documentation: https://docs.microsoft.com/en-us/dax/values-function-dax

There is also DISTINCT but VALUES allows for referential integrity check which makes it a better choice for most tasks. If you read through the documentation link I gave you'll get a more thorough explanation of what I mean by referential integrity check and practical difference between VALUES and DISTINCT.

And here's how to use VALUES for the scenario I just described.

Branch Targets = VALUES('Pizza Sales Data'[Branch])

And to finally make it meaningful. I add a New Column that assigns the target all branches are given (assuming Mega Pizza doesn't discriminate in its target allocation). :)