DAXDAX Trainer

DAX Concept

Dynamically Displaying Product Names Based on Filter Context Using VALUES and Conditional Logic

Learn to create a DAX measure that dynamically displays product names in a table, showing a single product, a list of multiple products, or a specific message for aggregated totals, leveraging the VALUES function.

IntermediateVALUESHASONEVALUECONCATENATEXISFILTERED

Concept Breakdown

The VALUES function returns a single-column table of unique values from the specified column in the current filter context. This is crucial for iterating over distinct items or determining the count of items in a context. When combined with HASONEVALUE, you can detect if only one distinct item exists. CONCATENATEX allows you to iterate over the values returned by VALUES and concatenate them into a single string. ISFILTERED is used to check if a specific column or table is being filtered, which is useful for custom behavior on total rows or specific aggregation levels.

What You Will Learn

  1. Understand when to use VALUES, HASONEVALUE, CONCATENATEX, ISFILTERED in a Power BI model.

  2. Practice the concept inside a real PBIX report rather than only reading syntax.

  3. Complete a intermediate-level task and verify the result with a hidden answer check.

Practice in Power BI

The starter PBIX link has not been attached yet. The student workflow is ready for the admin to connect the file.

  1. Download and unzip the provided exercise file, then open the Power BI workbook.

  2. In your Power BI Desktop file, navigate to the data model or report view where you can create a new measure.

  3. Create a new measure and name it 'Product list'.

  4. Implement the core logic: If there's only one distinct value for 'ProductName' in the current filter context, prepend 'Just ' to that product name. Otherwise, concatenate all distinct product names into a comma-separated list.

  5. To achieve step 4, use the VALUES function to get the distinct product names, HASONEVALUE (or COUNTROWS(VALUES(...))) to check for a single value, and CONCATENATEX to combine multiple names.

  6. Add an additional conditional check to your measure: If the 'FamilyName' column is being filtered (e.g., in a total row scenario where multiple products roll up to a family), display the text 'Too many products to list'. Use the ISFILTERED function for this.

  7. Place your newly created 'Product list' measure into the table visualization to observe the dynamic output.

  8. Save the file as 'Happier families'.

Starter DAX

Product list =

Expected Outcome

The 'Product list' column in the table will show 'Just [ProductName]' when only one product is in the filter context, '[Product1], [Product2], [Product3]' when multiple products are present, and 'Too many products to list' for aggregated rows (e.g., family totals).

Checking your sign-in status...