Disabling text field aggregation in matrix

[Anonymous]

Hey Everyone,
I have a dashboard using a matrix visualization to summarize some data. Some of the columns are text. The text columns are aggregated (first, last, count or count distinct) and I can't figure out how to turn that off and just return a blank value at the topmost levels instead. Picking the first/last value doesn't help me here, I would rather just have it blank. Can I make a column or measure in place that returns blank at the "ID" level and returns the non aggregated values for each date? I've tried this a few different ways but can't get it working. For example in the image below, we have 2 different values in "ID 2" for the "1422957" ID. 

But when we view it at the highest level (ID), it should just be blank instead of first or last text aggregation.

Thanks in advance:)

[v-stephen-msft]

Hi @Anonymous ,

HASONEVALUE funtion might be helpful to solve your issue.

Here's a simple sample. The return value corresponding to the ID is 0, as you said, you can put blank(), the return value corresponding to the date is 1, you can put the original measurement.

Measure = IF(HASONEVALUE('Table'[Date]),1,0)

Best Regards,

Stephen Tao

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

[amitchandak]

@Anonymous , isinscope can help for level

example blank for a level and for blank value of another level

if(isinscope(Table[ID]) || isblank(max(Table[ID2])), blank(), [measure])

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

[Anonymous]

Hey @amitchandak ,
Thank you for your reply. What is the "measure" you are using the the formula? I tried the following formula but is not working and it just returns blank for everything:

Measure = if(isinscope('Sign Billable Transactions'[end_user_id]) || isblank(max('Sign Billable Transactions'[echosignid])),BLANK(),"0")

 Both the ID fields are text fields and and one "ID" can have multiple "ID 2"