Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Joe_100
Helper I
Helper I

Matrix remove totals(first value) from text

Hi all,

 

I am trying to create a matrix that will have some text values in the columns.

Unfortunetaly Power Bi assumes that i want the first value as subtotal or grand total.

 

I almost got it working by creating a measure:

Text_Measure =

var CurrentItem = SELECTEDVALUE(Projects[KEY])

RETURN
SWITCH(CurrentItem;

CurrentItem;SELECTEDVALUE(Projects[Text]);

blank()

)

 

The only problem i run into now that it will still shows the first value in the subtotals if there is only ONE value.

 

See example below:

(First_text) is the "normal" column, Text_Measure is the measure i have created that is stated here above.

As you can see it only shows a subtotal if there is only one value.

2019.png

 

Someone has an idea how to remove the subtotal if there is only one value??

 

Many thanks!

2 REPLIES 2
Anonymous
Not applicable

Click the matrix, go to format, then subtotals and turn them off.

AnthonyTilley
Solution Sage
Solution Sage

i hope i understood what you where looking for 

 

use the hasonefilter function

 

i do not have your source data to replicate your measure but in the example below 

i have a text colunm that will say Project + project number as text but only in the subrows nothing shows in subtotals or grand totals as these are replaced with blank 

 

Measure = 

Var o = "PROJECT" & MAX('Table'[PROJECT])
var ret = if(HASONEFILTER('Table'[PROJECT]),o,blank())
return ret

 var o can be replaced with what ever you want to appear in your table 

var ret then checks if the value has only one filter for the project colunm 

 

sub.png





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.