[DAX] Concatenate labels per ID and date, then count distinct IDs by label combination

[dnaielpcamara]

Hi Community! I think this is impossible out of the box, I'm trying to find a solution for this issue any Ideas?

I have a 'Sample' table, to describe for each ID, what is the Label or labels that it has in a Day by Day context. Like so:

IDDateLabel

ID	Date	Label
foo	01/01/2025	X
foo	02/01/2025	Y
foo	02/01/2025	X
bar	01/01/2025	Y
bar	02/01/2025	Y
baz	01/01/2025	X
baz	02/01/2025	X
foo	03/01/2025	Y
bar	03/01/2025	Z
baz	03/01/2025	Z
baz	03/01/2025	Y
qux	01/01/2025	X
qux	02/01/2025	Y
qux	03/01/2025	Y

I want to be able to get, containing a count for each possible combination of label, with date as a possible slicer, here are two examples:

(no Filter applied):

Labels	Quantity
X,Y	2
X,Y,Z	1
Y,Z	1

If filter 02/01/2025 is applied:

Labels	Quantity
X	1
X,Y	1
Y	2

A solution using DAX Editor is:

DEFINE
	VAR __DS0FilterTable = 
		TREATAS({DATE(2025, 1, 2)}, 'Sample'[Date])

    VAR __DS0Core = 
        SUMMARIZECOLUMNS(
            'Sample'[ID],
            __DS0FilterTable,
            "Labels", CONCATENATEX('Sample','Sample'[Label],", ")
        )
	VAR __DS1Core = 
		SUMMARIZE(
			__DS0Core,
            [Labels],
			"DistinctCountID", DISTINCTCOUNT(Sample[ID])
        )

EVALUATE
	__DS1Core

However with this method I cant create the measures "Labels" and "DistinctCountID" and expect to work due to Power BI does not let me group by a dynamic, concatenated string like "X, Y".

Any Ideas?

**** UPDATE ****

Using DENEB I was able to create the behaviour I wanted. However I can't propagate filter from the Deneb to others visuals of the report. More details in my last comment. Any ideas to make this solution work with cross visuals filters or any better idea?

I also added more records in the Sample table.

Here is a sample PBI file:

https://app.powerbi.com/view?r=eyJrIjoiN2YzZTc1NGItYzVkZC00NGQ3LWE1MjUtN2RlNzdiOTRhYTJlIiwidCI6IjdkN...

[v-echaithra]

Hi  @dnaielpcamara ,

We’d like to follow up regarding the recent concern. Kindly confirm whether the issue has been resolved, or if further assistance is still required. We are available to support you and are committed to helping you reach a resolution.

Thank you for your patience and look forward to hearing from you.

Best Regards,
Chaithra E.

[v-echaithra]

Hi @dnaielpcamara ,

We would like to confirm if you've successfully resolved this issue or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.

Thank you for your patience and look forward to hearing from you.

Best Regards,
Chaithra E.

[v-echaithra]

Hi @dnaielpcamara ,

We wanted to follow up to see if the issue you reported has been fully resolved. If you still have any concerns or need additional support, please don’t hesitate to let us know, we’re here to help.

We truly appreciate your patience and look forward to assisting you further if needed.

Warm regards,
Chaithra E.

[dnaielpcamara]

Hi Community thanks for the support. I find out a partial solution for my issue. Using DENEB/Vega. However the issue that I'm getting now is, due the the aggregation done by deneb, when I select a value from the result, there is no filter propagation to the others visuals.

Here is the full Vega  (not vega-lite) code:

{
  "$schema": "https://vega.github.io/schema/vega/v5.json",
  "description": "Aggregate Label as Aggregator",

  "width": 400,
  "height": 180,
  "padding": {
    "bottom": 0,
    "left": 0,
    "right": 0,
    "top": 0
  },
  "signals": [
    {
      "name": "highlight", 
      "value": null,
      "on": [
        {
          "events": "rect:click, text:click",
          "update": "highlight === datum.Labels ? null : datum.Labels"
        }
      ]
    },
    {
      "name": "__selected__",
      "value": null,
      "on": [
        {
          "events": {"signal": "highlight"},
          "update": "highlight"
        }
      ]
    }
  ],
  "data": [
    { "name": "dataset" },
    {
      "name": "input",
      "source": "dataset",
      "transform": [
        { "type": "filter", "expr": "datum.Count__highlightComparator == 'eq'" },
        {
          "type": "aggregate",
          "groupby": ["ID"],
          "ops": ["mean", "values"],
          "fields": ["Count", "Label"],
          "as": ["Count", "LabelsArray"]
        },
        {
          "type": "formula",
          "as": "Labels",
          "expr": "join(pluck(datum.LabelsArray, 'Label'), ', ')"
        }
      ]
    },
    {
      "name": "release_counts",
      "source": "input",
      "transform": [
        {
          "type": "aggregate",
          "groupby": ["Labels"],
          "ops": ["sum"],
          "fields": ["Count"],
          "as": ["count"]
        },
        {
          "type": "collect",
          "sort": { "field": "Labels", "order": "acending" }
        }
      ]
    }
  ],

  "scales": [
    {
      "name": "xscale",
      "type": "band",
      "domain": { "data": "release_counts", "field": "Labels" },
      "range": "width",
      "padding": 0.1
    },
    {
      "name": "yscale",
      "type": "linear",
      "domain": { "data": "release_counts", "field": "count" },
      "nice": true,
      "range": "height"
    }
  ],

  "axes": [
    {
      "orient": "bottom",
      "scale": "xscale",
      "title": "Labels",
      "labelAngle": 45
    },
    {
      "orient": "left",
      "scale": "yscale",
      "title": "Count"
    }
  ],

  "marks": [
    {
      "type": "rect",
      "from": { "data": "release_counts" },
      "encode": {
        "enter": {
          "x": { "scale": "xscale", "field": "Labels" },
          "width": { "scale": "xscale", "band": 1 },
          "y": { "scale": "yscale", "field": "count" },
          "y2": { "scale": "yscale", "value": 0 },
          "tooltip": {
            "signal": "{ 'ReleaseID': datum.Labels, 'Count': datum.count }"
          }
        },
        "update": {
          "fill": [
            {
              "test": "datum.Labels === __selected__",
              "value": "#c8b20f"
            },
            {
              "value": "#e8d95a"
            }
          ],
          "opacity": [
            {
              "test": "(__selected__ != null && datum.Labels !== __selected__)",
              "value": 0.5
            },
            { "value": 1 }
          ]
        }
      }
    },
    {
  "type": "text",
  "from": { "data": "release_counts" },
  "encode": {
    "enter": {
      "x": { "scale": "xscale", "field": "Labels", "band": 0.5 },
      "y": { "scale": "yscale", "field": "count", "offset": -5 },
      "text": {"signal": "format(datum.count, ',')" },
      "align": { "value": "left" },
      "baseline": { "value": "middle" },
      "angle": { "value": -90 },
      "fill": { "value": "#333" },
      "fontSize": { "value": 11 }
    }
  }
}
  ],

  "config": {
    "view": { "stroke": "transparent" },
    "axis": { "grid": false }
  }
}

The solution part here is in the "data" section of the Vaga code. This is the result in the PBI Vega editor:

the dataset is the table that PBI passes to Deneb, with ID, Lables and a Distinct Count of ID:

Then we create an "input" table by:

1 - filter possible highlighted filters

2 - grouping by ID and summarizing count by mean (This cannot be done in the PBI side due to the date filter limitation)

3 - Add the "Labels" column.

Lastly we create a "release_counts" table by:

1 - Grouping by Labels and summing the "Count"

This table is used later in the rest of the code to build the chart show on all prints.

So I have one last issue. How Can I propagate the ID filter when selecting a column in this chart to the rest of the report when a value is highlighted?

Sample PBi Report: https://app.powerbi.com/view?r=eyJrIjoiN2YzZTc1NGItYzVkZC00NGQ3LWE1MjUtN2RlNzdiOTRhYTJlIiwidCI6IjdkN...

Any Ideas @Ashish_Mathur, @techies, @Rupak_bi ?

[v-echaithra · Accepted solution]

Hi @dnaielpcamara , 

Thank you for the update.

Deneb Vega/Vega-Lite visuals don’t automatically propagate filters to Power BI’s model. Unlike native visuals, Deneb doesn’t “know” how to tell Power BI what rows to filter unless you explicitly wire it up.
You can try this workaround to enable cross-filtering from a Deneb vega visual to the rest of a Power BI report, you need to leverage the special __selection__ (selected) signal, which allows Power BI to recognize and respond to interactions within the Deneb visual. Start by ensuring your dataset within Deneb includes the actual 'Sample'[ID] field — either as raw data or aggregated into arrays grouped by label combinations (e.g., "IDsArray").
When defining your Vega data transformations, use a values aggregation to collect all related IDs for each label combination. Then, define the __selection__ signal to capture interactions by setting it to datum.IDsArray upon a click event e.g., rect:click. This configuration emits the selected IDs back to Power BI when a label group like “X,Y” is clicked, allowing Power BI to filter other visuals that are connected to the 'Sample'[ID] field.
By structuring your visual this way, Deneb can participate in the report’s cross-filtering behavior just like native Power BI visuals.

Hope this helps,
Warm Regards
Chaithra E.

[Ashish_Mathur]

Hi,

How did you arrive at the answers in the first table?  Also, why is there no X,Y in table2?

[dnaielpcamara]

Hi @Ashish_Mathur, X and Y needs to be combined on the fly due to dates being used as filter and any range of dates can be selected.

I have a partial solution that I'm posting in a few minutes.

[techies]

Hi @dnaielpcamara Maybe you can first create a calculated column like this:

LabelsPerIDPerDate =
CALCULATE(
CONCATENATEX(
VALUES('Sheet8'[Label]),
'Sheet8'[Label],
",",
'Sheet8'[Label]
),
ALLEXCEPT('Sheet8', 'Sheet8'[ID], 'Sheet8'[Date])
)
This will give you a comma-separated list of labels per ID and Date.

Then, create a summary table like this to count each unique combination:

LabelSummary =
SUMMARIZE(
'Sheet8',
'Sheet8'[Date],
'Sheet8'[ID],
'Sheet8'[LabelsPerIDPerDate],
"Labels", 'Sheet8'[LabelsPerIDPerDate]
)
Finally, create a measure to count the combinations

[dnaielpcamara]

Thanks for the help @techies, however this cannot solve my issue. My end user was supose to be able to select any date context. So in pratice I have 11 months of this label data and they should be able to select any range os dates from the slicer. This method wont work for my situation.

[Rupak_bi]

Hi @dnaielpcamara ,

I am not able to understand the logic how the first table you are expecting without any date filter.

However, My calculation giving slight different result (as below) may be logical to you. 

I did this by creating a calculated table as below.

Let me know if this works or need some modification.

[dnaielpcamara]

Thanks for the help @Rupak_bi, however this cannot solve my issue. My end user was supose to be able to select any date context. So in pratice I have 11 months of this label data and they should be able to select any range os dates from the slicer. This method wont work for my situation.