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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Apply total value not to every coloumn

Hey guys,

 

do you know maybe if it is possible to decide on what coloumn to apply the total value? I have 3 measures (they have to stay measure, cannot change to coloumn because they are linked to a dynamic filter).

 

I would like to know if it is possible not to show for the BU Status?

 

Many thanks in advance,

 

Adam

 

apply total_not_to_every_coloumn.JPG

1 ACCEPTED SOLUTION

@Anonymous

 

Something like this ?

BU Status =
IF (
    ISFILTERED ( 'Incident Report'[Bus Unit] );   
UNICHAR ( IF ( 'Incident Report'[Measure2 as Text] = "0"; 10003; IF ( 'Incident Report'[Measure2 as Text] = "2"; 10007; IF ( 'Incident Report'[Measure2 as Text] = "1"; 33 ) ) ) ) )

where we're only adding the IF with the check  (in red) to what you already had.

View solution in original post

9 REPLIES 9
v-shex-msft
Community Support
Community Support

Hi @Anonymous,

 

I'd like to suggest you take a look at following blog to know how to use condition to confirm current hierarchy level,  then you can simply use if statement with that condition to replace specific level result.

Clever Hierarchy Handling in DAX

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
AlB
Super User
Super User

Hi @Anonymous

 

Yes, within your measures, you can tell whether you are at the Grand total row by testing with something like:

ISFILTERED(Table1[Bus Unit])

 

and then returning blank in that case (or any other thing you want the mesure to return).

 

Anonymous
Not applicable

hey @AlB,

 

thank you for your fast reply. 

 

could you please help me, where to put it? I have a measure, that is actually converting a measure into Unichar:

 

BU Status = UNICHAR(IF('Incident Report'[Measure2 as Text]="0";10003;IF('Incident Report'[Measure2 as Text]="2";10007;IF('Incident Report'[Measure2 as Text]="1";33))))
 
Measure2 as Text = FORMAT( CALCULATE(MAX('Incident Report'[ASPStatusID]);ALLEXCEPT('ServiceDashboard'; 'ServiceDashboard'[Bus Unit]);
FILTER('Incident Report';(CALCULATE(MIN('Incident Report'[Incident Start Date]))>=CALCULATE(MIN('CALENDAR'[Date])) && CALCULATE(MAX('Incident Report'[Incident Start Date]))<=CALCULATE(MAX('CALENDAR'[Date])+1)||CALCULATE(MIN('Incident Report'[Incident End Date]))>=CALCULATE(MIN('CALENDAR'[Date])) && CALCULATE(MAX('Incident Report'[Incident End Date]))<=CALCULATE(MAX('CALENDAR'[Date])+1)))
)+0;"")
 
Thank you in advance!
 
Many thanks,
 
Adam

@Anonymous

 

Something like this ?

BU Status =
IF (
    ISFILTERED ( 'Incident Report'[Bus Unit] );   
UNICHAR ( IF ( 'Incident Report'[Measure2 as Text] = "0"; 10003; IF ( 'Incident Report'[Measure2 as Text] = "2"; 10007; IF ( 'Incident Report'[Measure2 as Text] = "1"; 33 ) ) ) ) )

where we're only adding the IF with the check  (in red) to what you already had.

Anonymous
Not applicable

Hey @AlB,

 

I checked and it's unfortunately not working. 😕 However, for the ISFILTERED I put ServiceDashboard[Bus Unit], as it's only present in that database.

 

Maybe some other idea? 🙂 I would greatly appreciate!

 

Many thanks,

 

Adam

 

BU_Status_answer.JPG

@Anonymous

 

What's happening exactly? What is not working?

Can you share the pbix? We'd be faster like that

Anonymous
Not applicable

Hey @AlB,

 

sorry, I just had to re-create the table, afterwards it worked! Many thanks for it!

 

Do you know maybe, how it is possible to add a measure as a filter? - a workaround maybe?

 

The "Current Service Status" Measure is linked to the slicer, and changing based on the time.

 

The "Current Service Status Static" Coloumn is also linked to slicer, however not changing based on the time.

 

I would like to have however a chart, where I have an overview of the service statuses, within the chosen time horizon (Measure as Filter).

 

Any help would be greatly appreciated, here is the link for the pbtx. https://1drv.ms/u/s!Ah2VGOQRBzRVhijIn6jmykLe5DBS

 

Many thanks,

 

Adam

@Anonymous

You can use measures in the visual level filters

Anonymous
Not applicable

Hey @AlB,

 

it is somehow not working. The issue is the following: for the total period that is shown on the slicer 01/01/2018 till 31/12/2018 everything shows correct info (current service status as measure & current service status static as coloumn). Based on Current service static I could create the pie chart (based on current service status was not able).

 

The moment I change the time period, for example to 01/01/2018 till 05/11/2018 everything that is a measure is changing properly. However, the pie chart that is based on current service status static is not changing. 

 

See the attachments 🙂

 

My aim would be, that this is also chaning dynamically with the slicer. However, since this is not a measure I'm not sure how.. Even, when I put the measure to the visual level filter it's not correct, I think it should be the legend (not allowed).

 

Many thanks,

 

AdamOriginal.JPGChanged.JPG

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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