Filter measure result

DAX DAX Calculations
1

Hi there! very basic Q for the experts out here I suspect, yet I am forced to ask as I tried and did not get it straight…

image
image712×175 5.61 KB

Salaries =
CALCULATE(
[Act],
’Cost element’, ‘Cost element’[Cost elemt L3 (Description)] = “Hosted Employees recharges” ||
‘Cost element’, ‘Cost element’[Cost elemt L3 (Description)] = “Bonus / Profit sharing” ||
‘Cost element’, ‘Cost element’[Cost elemt L3 (Description)] = “Social charges - employee related” ||
‘Cost element’, ‘Cost element’[Cost elemt L3 (Description)] = “Salaries and other costs”
)

I would like to filter all Act values from Cost elemt 2 (which is Salaries and Social charges), except Indemnities / Exceptional costs (as underlined in red)

image
image819×406 27.4 KB

Notice how the model is set up:
image

Act =
DIVIDE( [(Base) Actuals], 1000 )

(Base) Actuals =
SWITCH(
TRUE(),
SELECTEDVALUE(‘Rate table ( For selection)’[Rate])=“Budget rate CY”,SUM(‘OPEX Actuals’[Amount Budget Rate CY]),
SELECTEDVALUE(‘Rate table ( For selection)’[Rate])=“Budget rate PY”,SUM(‘OPEX Actuals’[Amount Budget Rate PY]),
SELECTEDVALUE(‘Rate table ( For selection)’[Rate])=“Monthly Avg rate”,SUM(‘OPEX Actuals’[Amount Monthly Avg rate]),
SELECTEDVALUE(‘Rate table ( For selection)’[Rate])=“Local Currency”,SUM(‘OPEX Actuals’[Amount Local Currency]),
BLANK()
)

2

Hi @SamSPAIN .

To help us further analyze your current state and visualize your issue, could you please upload as many as you can of:

  • Your work-in-progress PBIX file, using sanitized data if necessary
  • Your dataset as an Excel file (again, sanitized if necessary)
  • A detailed mock-up (marked-up screenshot of the visual in question or Excel file) showing your desired outcome.

Also, if you provide DAX or Power Query code in your post, please format it using the built-in formatter.

Greg
_eDNA Forum - Format DAX or PQ

1 Like
3

OPEX.pbix (24.4 MB)

Hi @Greg, thanks for the heads-up! will keep all this in mind going forward.

Find attached a sample pbix file I came up with after watching @BrianJ tutorial on YouTube (thanks Brian for putting this together).

So for My Act figures (have it also for Fct and Bgt, in other tables that have been removed to make the file thinner) I would like to be able to isolate my “pure” Salary costs - excluding Indemnities.

image
image819×406 27.4 KB

Here the code:

(Base) Actuals =
SWITCH(
TRUE(),
SELECTEDVALUE(‘Rate table ( For selection)’[Rate])=“Budget rate CY”,SUM( **‘OPEX Actuals’** [Amount Budget Rate CY]),
SELECTEDVALUE(‘Rate table ( For selection)’[Rate])=“Budget rate PY”,SUM(‘OPEX Actuals’[Amount Budget Rate PY]),
SELECTEDVALUE(‘Rate table ( For selection)’[Rate])=“Monthly Avg rate”,SUM(‘OPEX Actuals’[Amount Monthly Avg rate]),
SELECTEDVALUE(‘Rate table ( For selection)’[Rate])=“Local Currency”,SUM(‘OPEX Actuals’[Amount Local Currency]),
BLANK()
)

Act = DIVIDE( [(Base) Actuals], 1000 )

This is the formula it’s giving me trouble:

image
image712×175 5.61 KB

My Q is, how can I use my Act measure to group all values under Cost element’[Cost elemt L2 (Description)] = Salaries and Social charges, excluding Indemnities / Exceptional costs

Thanks a lot for your help

4

Hello @SamSPAIN,

Thank You for posting your query onto the Forum.

The PBIX file which you’ve provided in that all of the measures are giving an error. Below is the screenshot provided for the reference -

image
image337×759 8.27 KB

In your file, you can try out the formula for Salaries and since you want to include all items from the “Salaries and Social Charges” except “Indemnities / Exceptional costs”. Below is the formula provided for the reference -

Salaries =
CALCULATE( [Act] ,
'Cost element'[Cost elemt L3 (Description)] = "Hosted Employees recharges" ||
'Cost element'[Cost elemt L3 (Description)] = "Bonus / Profit sharing" ||
'Cost element'[Cost elemt L3 (Description)] = "Social charges - employee related" ||
'Cost element'[Cost elemt L3 (Description)] = "Salaries and other costs" ||
'Cost element'[Cost elemt L3 (Description)] <> "Indemnities / Exceptional costs"
)

The reason why it’s giving you can error for this formula is because you’re referencing entire table everytime in the every line of the measure rather than referencing only the column. And therefore it gives an error. See the screenshot provided below for the reference where I’ve also replicated the same scenario in my file as well.

Error Result

But once we remove that table reference from the measure and we refer only particular column from that table than there won’t be any error. Below is the screenshot provided for the reference -

Final Results

Or you can upload the revised PBIX file with the measures working in it.

Hoping you find this useful and meets your requirements that you’ve been looking for. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

2 Likes
5

@Harsh, big thanks mate! Actually I think this code is even better and more readable:

Salaries = 
CALCULATE( [Act] ,
            'Cost element'[Cost elemt L3 (Description)] <> "Indemnities / Exceptional costs"
)

This one did the job perfectly well :slight_smile:

I won’t forget that tables shouldn’t be called within this syntax, only column reference.

Cheers

6

Hi @SamSPAIN. A few notes after a quick review:

  • your PBIX has 2 dates tables, [Calendar] and [Dates]; [Calendar] is marked as a date tables
  • your [(Base) Actuals] measure refers to columns that are not in your dataset, so the measure errors
  • your [Act] measure branches off of the [(Base) Actuals] measure, so it errors too
  • the FILTER clause in your [Salaries] measure should be revised to use proper syntax, for example
Salaries 2 = 
CALCULATE(
    [Act],
    FILTER( 'Cost element', 
        'Cost element'[Cost elemt L3 (Description)] = "Hosted Employees recharges" ||
        'Cost element'[Cost elemt L3 (Description)] = "Bonus / Profit sharing" ||
        'Cost element'[Cost elemt L3 (Description)] = "Social charges - employee related" ||
        'Cost element'[Cost elemt L3 (Description)] = "Salaries and other costs" )
)
  • you may also wish to re-write the [Salaries] measure to be exclusionary instead, something like
Salaries 3 = 
CALCULATE(
    [Act],
    FILTER( 'Cost element', 
        'Cost element'[Cost elemt L3 (Description)] <> "Indemnities / Exceptional costs" )
)
  • in any event, both still refer to the [Act] measure which errors, so they in turn will error.

Once you’ve rectified these errors, if you need further assistance from the forum members, please post your revised PBIX.
Greg

2 Likes
7

Hello @SamSPAIN,

You’re Welcome. :slightly_smiling_face:

I’m glad that I was able to assist you and you found it helpful.

Another very important thing that you can note in this type of problems is that, when you reference the same columns i.e. in this case you were referencing one single column from a particular table. So in situation like this depending upon your requirement you can use “||” or “&&” condition without using “FILTER” function. But when you start referencing multiple columns you cannot use “&&” condition simply than in that case you’ll be compelled to wrap the conditions inside the “FILTER( ALLSELECTED() )” or “FILTER( ALL() )” depending upon the requirement to achieve the end results.

Conclusion: So you need to use FILTER function when multiple columns are referenced as a condition since CALCULATE function cannot evaluate the results as a TRUE/FALSE expression. And also when you use measure as reference for conditions than in that case as well you’ll be required to use FILTER function since we cannot use the measures directly and place them under the FILTER condition as an arguement .

Hoping this will be helpful to you for your future requirements. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

8

@Greg - thanks for your reply. My apologies, somehow I forgot to update the (Base) Actuals code which led all formulas to error. Please find attached amended PBIX.

Meanwhile I was working I realized that Salaries code, doesn’t work if Cost elemt 2 (Description) is not selecting Salaries and Social charges - ie. it takes everything but Indemnities.

Salaries = 
CALCULATE(
    [Act],
    FILTER( 'Cost element', 
        'Cost element'[Cost elemt L3 (Description)] <> "Indemnities / Exceptional costs" )
)

Any hint you can share here on how to accomplish this best?

image
image836×502 38.5 KB

image
image873×625 47.5 KB

I created a copy of the Calendar table, Date table, to try replicate this analysis recently posted from SQLBI without much success - ie. totals not disaggregated but showing in one shot as per month selected. This might be needed to post it into another thread.

OpEx Act P6M = 
VAR _NumOfMonths = 6
VAR _StartDate = MAX( 'Calendar'[Date] )
VAR _PriorDate = 
    DATESINPERIOD(
        'Dates'[Date],
        _StartDate,
        -_NumOfMonths,
        MONTH
    )
VAR _Result=
    CALCULATE(
        [Act],
        REMOVEFILTERS( 'Calendar' ), 
        KEEPFILTERS( _PriorDate ),
        USERELATIONSHIP( 'Calendar'[Date], Dates[Date] )
    )

RETURN
    _Result

image
image1686×617 100 KB

9

Hi @SamSPAIN. You’re posting to a solved thread … for maximum visibility, please create a new thread with a new question. Also, it’s not clear what your issue is (" … how to accomplish this best …"): could you please elaborate on exactly what’s currently happening and exactly what is your desired outcome (and upload an Excel mock-up of the desired outcome) for the forum members to review?
Greg

1 Like