Combination of two column values in DAX measure

[rob_vander2]

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?

[sjoerdvn · Accepted solution]

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.

[v-yohua-msft · Accepted solution]

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.

[sjoerdvn · Accepted solution]

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.

[sjoerdvn]

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"

[rob_vander2]

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

[sjoerdvn]

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

[rob_vander2]

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