topic Combination of two column values in DAX measure in DAX Commands and Tips

Combination of two column values in DAX measure

rob_vander2 — Thu, 11 Apr 2024 12:50:34 GMT

Hi All,

I have below dataset

Cat	Name	Value
a	aa	100
a	bb	50
a	cc	30
b	aa	25
b	cc	65
c	aa	70
c	bb	54
c	dd	34

I want to create visual report, with all combination values of Cat,Name like below. Wherever combination is not available in data, value should be blank in visual report

Cat	Name	Value
a	aa	100
a	bb	50
a	cc	30
a	dd
b	aa	25
b	bb
b	cc	65
b	dd
c	aa	70
c	bb	54
c	cc
c	dd	34

I am struggling to create DAX measure for it. What is the best way to achive this?

Re: Combination of two column values in DAX measure

sjoerdvn — Thu, 11 Apr 2024 13:05:56 GMT

If columns "Cat" and "Name" are in the same table, and they are used in the same (table) visual, Power BI will only show the existing combinations. To get around this, you need a dimension table for one or both columns. And even then, if all measures produce blanks, it supresses showing these rows. You would thean need a measure that returns for instance "0" or "blank"

Re: Combination of two column values in DAX measure

rob_vander2 — Thu, 11 Apr 2024 13:08:05 GMT

sjoerdvn Yes both are in same table and need to be used in same visual. Please advice. Could you share pbix file with solution?

Re: Combination of two column values in DAX measure

sjoerdvn — Thu, 11 Apr 2024 13:24:55 GMT

Solution would depend on data source (file, SQL table etc.). Can you share what you have so far (with dummy data).

Re: Combination of two column values in DAX measure

rob_vander2 — Thu, 11 Apr 2024 13:56:24 GMT

Hi, I already shared sample data in my post. Could you use to build solution?

Re: Combination of two column values in DAX measure

sjoerdvn — Thu, 11 Apr 2024 14:50:33 GMT

I used the html to get the table data, and added dimensions in power query (there are many different ways of doing this, depending on data source and personal preferences)
I have added the relationships and created measures for examples with either a "0" or a blank with an extra column.

Re: Combination of two column values in DAX measure

Anonymous — Mon, 22 Apr 2024 06:47:45 GMT

Hi, rob_vander2

Thanks for the reply from @SuperUser/@NormalUser, please allow me to provide another insight:

You can create a new table, use the crossjoin function

Table 2 = CROSSJOIN(VALUES('Table'[Cat]),VALUES('Table'[Name]))

And then compare it with the original table, and return a blank if no combination exists in the original table.

Column = LOOKUPVALUE('Table'[Value], 'Table'[Name],'Table 2'[Name], 'Table'[Cat],'Table 2'[Cat] )

In the Table visual, if the number in a column is empty, it will automatically aggregate, and if you want to show white space in the Table visual, you need to put the DAX Measure with data into the view to show the previous white space

Measure = IF(MAX('Table 2'[Column]) = BLANK(),0,MAX('Table 2'[Column]))

My preview like this:

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.