Apply total value not to every coloumn

[Anonymous]

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

[v-shex-msft]

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

[AlB]

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]

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

[AlB · 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.

[Anonymous]

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

[AlB]

@Anonymous

What's happening exactly? What is not working?

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

[Anonymous]

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

[AlB]

@Anonymous

You can use measures in the visual level filters

[Anonymous]

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,

Adam