Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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
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/
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
don't summarize any columns, enable "show items with no data"
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 =
Thanks,
Mannu
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
46 | |
31 | |
30 | |
18 | |
18 |