Calculate and IF error - not enough arguments

[Anonymous]

Hello Community  -  I have this calculated column.  At first I built it without using Calculate, then got an error saying that Calculate had to be used if using the USERELATIONSHIP function.  

So I added Calculate.   Now I'm getting an "argument" error, and I'm not sure where to place the additional arguments?  

Leadtime Rules =

IF (

'Sf_Opportunity Line'[Opp Line Sum of Total Price] <= 40000

&& 'Sf_Opportunity Line'[Status__c] = "Open"

&& CALCULATE(DATEDIFF (USERELATIONSHIP('Dim_Date Table'[Date],

'Sf_Opportunity Line'[Customer_Requested_Ship_Date__c]),

TODAY (),

DAY

) <= -15

&& CALCULATE(DATEDIFF (USERELATIONSHIP('Dim_Date Table'[Date],

'Sf_Opportunity Line'[Customer_Requested_Ship_Date__c]),

TODAY (),

DAY

) > -1400,

"Upcoming Zone",

IF (

'Sf_Opportunity Line'[Opp Line Sum of Total Price] <= 40000

&& 'Sf_Opportunity Line'[Status__c] = "Open"

&& CALCULATE(DATEDIFF (USERELATIONSHIP('Dim_Date Table'[Date],

'Sf_Opportunity Line'[Customer_Requested_Ship_Date__c]),

TODAY (),

DAY

) <= -8

&& CALCULATE(DATEDIFF (USERELATIONSHIP('Dim_Date Table'[Date],

'Sf_Opportunity Line'[Customer_Requested_Ship_Date__c]),

TODAY (),

DAY

) >= -14,

"Danger Zone",

)

[jdbuchanan71 · Accepted solution]

You just need to set the formatting of the background based on the Field Value of the formatting measure.  Here is an example from one of my files.

FormattingMeasure = 
VAR _LogType = SELECTEDVALUE(Log_Table[Log_Type])
RETURN
SWITCH(
    _LogType,
    "HEDIS_RESULT_AMB_TIMER","Red",
    "HEDIS_RESULT_BCS_TIMER","Blue")

[parry2k]

@Anonymous @jdbuchanan71 is doing a great job getting you across the line. All the best!! You are in safe hands 👍

But one suggestion I want to make, whenever you are posting a question, better to create a simple sample data, share it in a table format, with the expected output and what you have tried so far and what error or explain what is not working.

You started this post by showing your DAX code and asking CALCULATE - IF issue using USERELATIONSHIP which has nothing to do with the problem you are trying to solve. Just my 2 cents. It helps to provide a more precise and quick solution and helps everyone. CHeers!!

[jdbuchanan71]

You would not add it as a calculated column.  You just need the switch measure to read the value and return the correct formatting string.

FormattingMeasure = 
VAR _Zone = SELECTEDVALUE ( 'Sf_Opportunity Line'[Leadtime Rules] )
RETURN
	SWITCH 
		(_Zone,
		"Upcoming Zone","blue",
		"Danger Zone","red"
		)

[Anonymous]

@jdbuchanan71     Hi JD  -  My conditional value is on a monetary value in this table.   If the resulting value (from the Leadtime rules measure) matches the criteria, then the conditional formatting should be applied.  Previously, I was able to create a group to achieve this by assigning each status to a particular number (1, 2, 3, etc) and then using conditional formating on my monetary value).   So, if a particular "cell" on the matrix fit the criteria, then it would get color coded a certain color.  

I am not able to use a measure in the conditional formating area it seems.   Is there a way to color code using DAX?   

@parry2k    -  I agree with your sentiment in general, but this is all related to my original question.  

[jdbuchanan71]

Try using just the value in the column in a conditional formatting measure.  Something like this.

FormattingMeasure = 
VAR _Zone = SELECTEDVALUE ( 'Sf_Opportunity Line'[Leadtime Rules] )
RETURN
	SWITCH 
		(_Zone,
		"Upcoming Zone","blue",
		"Danger Zone","red"
		)

Then use the value of the FormattingMeasure to set the conditional formatting on the field.  No need to add another grouping.

[Anonymous]

Ok....so I fixed the Group issue.    In the calculated column, there was no final argument.  This caused the group that I created to see "blank" values that were values after the last IF clause.   I  added an "NA" for the "false" part of the statement which then gave the group a value to assign.   It did not seem to know what to do with those values, but now it is working fine.   

Thanks again...saved my day! 

[jdbuchanan71]

I'm not sure what would be causing the error.  Are you able to share your .pbix file with us?

[Anonymous]

@jdbuchanan71   @BA_Pete   @parry2k     First...thanks everyone for their feedback and input.   I think JD's solution will work, but I am getting odd behavior if I create a group from this calculated column.  If I set the value of the buckets to Text...no problem.   But if I set to whole number, the group just shows as an error.    I have a lot of other buckets not shown in my original formula.   The idea is that I will assign a numeric value (using groups) to each "zone".   Then, use conditional formatting.   1= blue   2= red....etc    So that is why I need the group to be a numeric data type (not text).      Unfortunately I cannot share the dataset as it is a corporate dataset.   

[jdbuchanan71]

@Anonymous 

DATEDIFF does not need a date table, it just needs 2 dates.  In your case those are.
'Sf_Opportunity Line'[Customer_Requested_Ship_Date__c]
and
TODAY()
Just as an example this is a valid DATEDIFF measure and no date table is used.

[Anonymous]

Since my group idea is not working, I was thinking to add a measure that tries to accomplish the same thing.  I was going to use SWITCH, but having an issue getting it to work on a calculated column.   Should I try something different?   

[jdbuchanan71]

@Anonymous 

I don't think you need USERELATIONSHIP at all.  Assuming you are wanting to add the column to the 'Sf_Opportunity Line' table, give this a try.

Leadtime Rules =
VAR _Days =
    DATEDIFF (
        'Sf_Opportunity Line'[Customer_Requested_Ship_Date__c],
        TODAY (),
        DAY
    )
RETURN
    IF (
        'Sf_Opportunity Line'[Opp Line Sum of Total Price] <= 40000
            && 'Sf_Opportunity Line'[Status__c] = "Open"
            && _Days <= -15
            && _Days > -1400,
        "Upcoming Zone",
        IF (
            'Sf_Opportunity Line'[Opp Line Sum of Total Price] <= 40000
                && 'Sf_Opportunity Line'[Status__c] = "Open"
                && _Days <= -8
                && _Days >= -14,
            "Danger Zone"
        )
    )

[Anonymous]

@jdbuchanan71    I tried this and it did indeed work for the calculated table.  Very clean solution.   The odd thing is, if I create a "group" from this, which I use for color formating (1 = Danger Zone, 2 = Upcoming, etc), the group results in an error.    I have the group formatted as a whole number, (to work for conditional formatting).   Not sure what could be causing this...any idea?  

[BA_Pete]

Hi @Anonymous ,

At a quick glance, it looks like you've not balanced out your brackets (parentheses).

For example, in the following screenshot, you've opened three brackets within the calculation but only closed two:

If you get these types of argument errors, just go through each of your calculation blocks counting on your left hand each time a bracket is opened, and on your right hand each time one is closed. It will give you the answer 90% of the time.

Beyond that, it looks like the structure of your calculate calculations are incorrect, for example, USERELATIONSHIP should be a filter argument within CALCULATE, not a function in and of itself.

Pete

[parry2k]

@Anonymous it is not correct syntax, first argument suppose to expression (usually aggregation) and return some scalar value CALCULATE function (DAX) - DAX | Microsoft Docs

for example: 

 CALCULATE(DATEDIFF ( MAX ( 'Dim Table'[Date] ), USERELATIONSHIP('Dim_Date Table'[Date],
'Sf_Opportunity Line'[Customer_Requested_Ship_Date__c]) )

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

@parry2k   Thanks Parry.   I've tried adding your formula and just getting stuck on one part.   Can you advise where to fix this?  It's important that I keep the logic that specifies the number of days.