Scalar Value, dealing with text convert DAX Table to Measure

[Aucesar]

Hi Community,

I could create a table with this:

NewTable = SUMMARIZE(tableA, TableA[Shift1], "NewColunm", DISTINCTCOUNT(TableA[ProductionBtach]))

Everithing good, but when I tried to use as Measure, I got "multiple columns cannot be converted to a scalar value". So my question is how to transform DAX above to be used as measure? both colunms are text.

[Semaj06]

Hi

You cannot use it as a measure, you are returning 2 columns and with a measure you have to return only 1 value.

If you want to claculate, just do a measure with the countrows part and just publist the shift colum on the display and your new measure.

[Aucesar]

Hi @vicky_ sorry, didn´t put any date, basically as below:

	Shift1	Shift2	Desire Result
ProductionBatch	Worker	Worker2	Employee	ProductionBatch-Worked
A1	Lois	Reggie	Lois	2
A1	Lois	Reggie	Reggie	3
A1	Reggie	Lois	Dannie	2
A1	Dannie	Lois
B1	Lois	Reggie
B1	Reggie	Lois
B1	Reggie	Lois
C1	Reggie	Dannie		A1
C1	Dannie	Reggie
C1	Reggie	Dannie
C1	Dannie	Reggie
A1	Lois	Reggie
A1	Lois	Reggie
A1	Reggie	Lois
A1	Dannie	Lois
B1	Lois	Reggie
B1	Reggie	Lois
B1	Reggie	Lois
C1	Reggie	Dannie
C1	Dannie	Reggie
C1	Reggie	Dannie
C1	Dannie	Reggie

[v-zhangti]

Hi, @Aucesar 

You can try the following methods.

Measure = Var _table=SUMMARIZE(TableA, TableA[Worker], "NewColunm", DISTINCTCOUNT(TableA[ProductionBatch]))
Return
MAXX(_table,[NewColunm])

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[Aucesar]

Hi @v-zhangti put sample data, with comments, if you could take a look, your data sample and example, worked for colum Worker1 but when I use on worker2 in the real data, doesn´t worked as expected;
Give a try when possible;
https://1drv.ms/u/s!AktM4_8VYySOiJMb5iee9iC3tU0cpA?e=3Okqh1

[v-zhangti]

Hi, @Aucesar 

You can try the following methods.

Measure 1 = Var _table=SUMMARIZE(Refugo, Refugo[Ajudante],Refugo[Numero Fusao], "NFusoesAjudante", DISTINCTCOUNT(Refugo[Numero Fusao]))
Return
SUMX(_table,[NFusoesAjudante])

Measure 2 = Var _table= SUMMARIZE(Refugo, Refugo[Moldador],Refugo[Numero Fusao], "NFusoesMoldador", DISTINCTCOUNT(Refugo[Numero Fusao]))
Return
SUMX(_table,[NFusoesMoldador])

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[Aucesar]

Hi, @v-zhangti not yet, if I do the sum from both measure values are not correct;

Also as the real data is available now, we could try using Refugo Table which is the main table, fell free , any help is welcome!

[v-zhangti]

Hi, @Aucesar 

What is the correct total value you expect?

Best Regards

[Aucesar · Accepted solution]

Hi @v-zhangti I forgot to close this topic I figured out using another method, in my calculation I did the "removefilters" and it worked as expected. Thanks for your time, this the expected result.

 

[vicky_]

Like you said, you're creating a table with that DAX function you've written. 

If you need it to display as a measure in a table, you need to tell powerBI how to summarize the table to return only 1 value. Without more specifics, it's hard to tell you how to summarize it, but you can use functions such as MAX, COUNT, etc.