Display field value on rows and count in the total

[Anonymous]

Hi,

I have a unique requirement, where I need to display textual content applicable in the table rows, while displaying the distinct count of the same in total. Each field displayed in the table may be filtered.

Is it possible to create a DAX expression/ is there a work around to meet this requirement?

For example, in the snapshot below, I m displaying distinct count of Column B elements in the total.

However, the expression fails when there is a single element as it was not possible to distingish between a row item and total

DAX I used:

ColB or Distinct Count =

var _x = DISTINCTCOUNT(Test[ColB])

var _y = COUNTROWS(Test)

RETURN

IF(_y=1, IF(_x=1  ,  SELECTEDVALUE(Test[ColB]), "1"), _x & "")

As Expected

Sample data

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY7BCQAxCAR78Z1H1NjAWYak/zaSjSTgfQYZZsEIYmr0AQ5wp9mCBBJw4CgdcHrDdEVJ/8m3PVPA4TU7g7TSSfllZMfvF1y25Vw=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ColA = _t, ColB = _t, ColC = _t, ColD = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ColA", Int64.Type}, {"ColB", type text}, {"ColC", type text}, {"ColD", Int64.Type}})
in
    #"Changed Type"

Thanks,

Mannu

[lbendlin]

In this particular scenario ISINSCOPE is better than HASONEVALUE. That is not universally true.

Measure = if(isinscope(Query1[ColB]),max(Query1[ColB]),DISTINCTCOUNT(Query1[ColB]))

https://www.sqlbi.com/articles/distinguishing-hasonevalue-from-isinscope/ 

[Anonymous]

Thanks @lbendlin 

I m still unable to get the totals in. Infact now, Im unable to get the item values correctly (it shows count many a time)

regards,

Mannu

[lbendlin]

don't summarize any columns, enable "show items with no data"

[Anonymous]

Thanks again @lbendlin 

I have done the same. But then I see the distinct count on each row.

It only works oif I have Notiifcation Count column on table (which defeats the purpose of designing this Measure)

Combination of order No and Notification No are Unique in this table.

All fields are text fields. So converting it into text

No/Count = 

if(isinscope(Order[Notification No]),max(Order[Notification No]),DISTINCTCOUNT(Order[Notification No])&" ")

Thanks,

Mannu