unique ID in visualization by using measure column based on the data table

[Saxon10]

Hi,

How can I get the “item” code only in visualization by using measure column based on the data table.

The “item” code not repeated in my data table but there is some duplication entry according to the item code in my data table.

When I try to use the summarise function in visual I am receiving the following error message “  A table of multiple value was supplied where a single value was expected”

Data Table:

ITEM	FRUIT LIST	AREA CODE	LANGUAGE	SALES CODE
123	Avocado	3100	EN	EU01
124	Avocado	3100	EN	US
125	Avocado	3100	EN	UK
126	Avocado	WEST	EN	UK
127	Avocado	3100	EN	UK
128	Avocado	3100	EN	UK
129	Avocado	3100		UK
130	Avocado	3100	EN	INDIA01
131	Avocado	3100	EN
234	Star Fruit	WEST	EN	INDIA01
235	Star Fruit	NORTH	EN	INDIA01
236	Star Fruit	EAST	EN	INDIA01
237	Star Fruit	SOUTH	EN	INDIA01
238	Star Fruit	WEST	EN	INDIA02
239	Star Fruit	NORTH	EN	INDIA02
240	Star Fruit	EAST	EN	INDIA02
241	Star Fruit	SOUTH	EN	INDIA02
56	Watermelon		EN	UK

Result in visualization.

ITEM
123
124
125
126
127
128
129
130
131
234
235
236
237
238
239
240
241
56

[Anonymous · Accepted solution]

Hi  @Saxon10 ,

Using column will automatically summarize. Using measure, if there is only one measure column, only one word will be displayed, which should be used with other columns

If you want to use column, you can use do not summarize next to it

If you want to use measure, you can associate it with other columns and hide them with a white background color

1. Create measure.

Measure = MAX('Table'[Item])

2. File formatting – select the column to hide, select font color and background color as white, and apply color as white

3. Result:

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

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

[Saxon10]

thanks for your time and sorry for the late reply. Your soloution working well.

[ryan_mayu]

@Saxon10 

why not just use a table visualization to display item and don't summarized?

[Saxon10]

thank you so much for your respones. I am aware of this option. Can you please advise how can I get the same result by using measure column in visual?

[ryan_mayu]

@Saxon10 

maybe can try to create a new table

Table 2 = DISTINCT('Table'[ITEM])

Table 2 = VALUES('Table'[ITEM])

[AllisonKennedy]

@Saxon10  Sorry, I'm not clear on what your question is. 

The visual table in your second screenshot - is that your desired result or what you currently have? 

What does the raw data model look like? 

You may find either RELATED(table[itemcode]) or SELECTEDVALUE(table[itemcode]) helpful in your measure.

If that doesn't help, please share the formula for the measure you're trying to use and also your relationships/data model view. Thanks!

[Saxon10]

thanks for your reply.

The visual table in your second screenshot - is that your desired result or what you currently have? 

Yes. That's my desired result. I don't have anything in visual. 

What does the raw data model look like? 

My data look like

ITEM

FRUIT LIST

AREA CODE

LANGUAGE

SALES CODE

123

Avocado

3100

EN

EU01

124

Avocado

3100

EN

US

125

Avocado

3100

EN

UK

126

Avocado

WEST

EN

UK

127

Avocado

3100

EN

UK

128

Avocado

3100

EN

UK

129

Avocado

3100

UK

130

Avocado

3100

EN

INDIA01

131

Avocado

3100

EN

234

Star Fruit

WEST

EN

INDIA01

235

Star Fruit

NORTH

EN

INDIA01

236

Star Fruit

EAST

EN

INDIA01

237

Star Fruit

SOUTH

EN

INDIA01

238

Star Fruit

WEST

EN

INDIA02

239

Star Fruit

NORTH

EN

INDIA02

240

Star Fruit

EAST

EN

INDIA02

241

Star Fruit

SOUTH

EN

INDIA02

56

Watermelon

EN

UK

Result:

 

I tried the both function but it's not working.

can you please advise.