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:ITEMFRUIT LISTAREA CODELANGUAGESALES CODE123Avocado3100ENEU01124Avocado3100ENUS125Avocado3100ENUK126AvocadoWESTENUK127Avocado3100ENUK128Avocado3100ENUK129Avocado3100 UK130Avocado3100ENINDIA01131Avocado3100EN 234Star FruitWESTENINDIA01235Star FruitNORTHENINDIA01236Star FruitEASTENINDIA01237Star FruitSOUTHENINDIA01238Star FruitWESTENINDIA02239Star FruitNORTHENINDIA02240Star FruitEASTENINDIA02241Star FruitSOUTHENINDIA0256Watermelon ENUK Result in visualization. ITEM12312412512612712812913013123423523623723823924024156

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 color1. 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 white3. Result:You can downloaded PBIX file from here. Best Regards,Liu YangIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

Saxon10

Post Prodigy

5 years ago

Solved

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

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

Need Help

Anonymous
5 years ago
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.

7 Replies

AllisonKennedy
Community Champion
5 years ago
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
  Post Prodigy
  5 years ago
  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.
ryan_mayu
Super User
5 years ago
Saxon10

why not just use a table visualization to display item and don't summarized?
- Saxon10
  Post Prodigy
  5 years ago
  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
    Super User
    5 years ago
    Saxon10
    
    maybe can try to create a new table
    
    Table 2 = DISTINCT('Table'[ITEM]) Table 2 = VALUES('Table'[ITEM])
Anonymous
Not applicable
5 years ago
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
  Post Prodigy
  5 years ago
  thanks for your time and sorry for the late reply. Your soloution working well.

Forum Discussion

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

7 Replies

Related content

Unique Count in Row convert

unique value in measure column

How to show multiple metrics/measures in a visual?

Visualize bins with unique measure values

Measure Issue in visual table