Sum filter with more than 3 arguments?

[Anonymous]

My goal is to sum up three different wage distribution containers, with a filter for both the year, and the geography. Including the year was no problem. However, including the geography too, broke the filter. OR only accepts two arguments, so I'm unsure how to add three, or even more filters.

Under 30k = 
CALCULATE(
    SUM('Wage Distribution'[Total Population]),
    FILTER (
            'Wage Distribution',
        OR(OR (
            'Wage Distribution'[Wage Bin]="< $10K",
            'Wage Distribution'[Wage Bin]="$10-20k"),
            'Wage Distribution'[Wage Bin]="$20-30k",
            
        )
            && 'Wage Distribution'[ID Year]=2018
            && 'Wage Distribution'[Geography]="Florida"
    )
)

How do I add in 'Wage Distribution'[Geography]="Florida", or even, another filter after that? 

[Anonymous]

Hi @Anonymous,

It seems like you are try to do rolling calculations based on a text field.

In my opinion, I'd like to suggest you add a calculated field to extract the 'bin range'(numeric type) from the text value field.
Then you can use simple conditions in your formula for cumulative calculations without broken/effect on filters and your records.

Regards,

Xiaoxin Sheng

[AllisonKennedy]

@Anonymous
Hi! Looks like you are struggling with this particular measure.

Couple of things, first:
AND() and OR() DAX functions only accept two arguments, but you may use && and || operators instead, so

OR (
'Wage Distribution'[Wage Bin]="< $10K",
'Wage Distribution'[Wage Bin]="$10-20k")

is the same as


'Wage Distribution'[Wage Bin]="< $10K"
||
'Wage Distribution'[Wage Bin]="$10-20k"

That way you may add as many conditions in the filter as you want.

Second: You need to logically decide when to use AND /&& versus when to use OR/ ||

Your formula above looks ok for adding geography, but depends if you actually have data for 2018 Florida in one of those wage distributions?

Final thought: What is your ultimate goal? This looks like a lot of specific filters to place in a measure and this could be done using slicers and report visuals.
https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-slicers

You can even use Grouping to group the wage distribution how you would like:
https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-grouping-and-binning

Hope that helps some!

Cheers!

[amitchandak]

@Anonymous , try like

Under 30k = 
CALCULATE(
    SUM('Wage Distribution'[Total Population]),
    FILTER (
            'Wage Distribution',
            'Wage Distribution'[Wage Bin] in {"< $10K","$10-20k","$20-30k"}
            && 'Wage Distribution'[ID Year]=2018
            && 'Wage Distribution'[Geography]="Florida"
    )
)

[harshnathani]

Hi @Anonymous ,

Try IN instead of OR

Under 30k =
CALCULATE (
    SUM ( 'Wage Distribution'[Total Population] ),
    FILTER (
        'Wage Distribution',
        'Wage Distribution'[Wage Bin]
            IN {
            "< $10K",
            "$10-20k",
            "$20-30k"
        }
            && 'Wage Distribution'[ID Year] = 2018
            && 'Wage Distribution'[Geography] = "Florida"
    )
)

Else use ||  

Regards,

Harsh Nathani

[Syndicate_Admin]

Excellent response, not wise who accepted the IN function in DAX, that is very functional. You have opened up the picture for me. Thank you very much, excellent contribution.