cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sean Super Contributor
Super Contributor

Re: What I Thought would be simple...I need help!

@mrcooper or you can just include it in the SWITCH like this on top Smiley Happy

 

PastDueStatus COLUMN 3 = 
SWITCH (
    TRUE (),
    ISBLANK('Table'[DaysPastDue]), "No Data",
    'Table'[DaysPastDue] <= 0, "Current",
    'Table'[DaysPastDue] > 0 && 'Table'[DaysPastDue] <= 30, "1-30 Days Past Due",
    'Table'[DaysPastDue] > 30 && 'Table'[DaysPastDue] <= 60, "30-60 Days Past Due",
    'Table'[DaysPastDue] > 60 && 'Table'[DaysPastDue] <= 90, "60-90 Days Past Due",
    "Over 90 Days Past Due"
)

 

2016-10-22 - SWITCH - DaysPastDue2.png 

mrcooper Frequent Visitor
Frequent Visitor

Re: What I Thought would be simple...I need help!

I just finished putting it all together....

 

AR Status = SWITCH(true(),
ISBLANK('BI - GS Aged AR'[DaysPastDue2]), "No Data",
'BI - GS Aged AR'[DaysPastDue2] <=0 , "Current",
'BI - GS Aged AR'[DaysPastDue2] >=1 & 'BI - GS Aged AR'[DaysPastDue2] <=30 ,"1-30 Days Past Due",
'BI - GS Aged AR'[DaysPastDue2] >=31 & 'BI - GS Aged AR'[DaysPastDue2] <=60 ,"30-60 Days Past Due",
'BI - GS Aged AR'[DaysPastDue2] >=61 & 'BI - GS Aged AR'[DaysPastDue2] <= 90, "60-90 Days Pat Due",
'BI - GS Aged AR'[DaysPastDue2] >=91, "Over 90 Days Past Due"
)

 

It looks like we have a winner....Then it bombs with...

 

"DAX comparison operations do not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values"

 

What format should the "DaysPastDue" column be in order for this to work without getting any trickier with the code?

Sean Super Contributor
Super Contributor

Re: What I Thought would be simple...I need help!

@mrcooper Do you import the results in DayPastDue? Strange that they are not Number already?

 

For quick Visual reference of the Type in a Column => left-justified is TEXT and right-justified Number/Date?

 

 

mrcooper Frequent Visitor
Frequent Visitor

Re: What I Thought would be simple...I need help!

They are imported, but in the system they come from they are calculated fields.  It comes across as a decimal number.

 

I was thinking of duplicating that row and converting it then build the same formula with the updated name.  The problem is, I don;t know what format the DaysPastDue2 and what the newly created column should be,

Sean Super Contributor
Super Contributor

Re: What I Thought would be simple...I need help!

I would go with Whole Number - unless you are using date and time to calculate the difference and really getting decimal numbers.

 

But either number should work.

mrcooper Frequent Visitor
Frequent Visitor

Re: What I Thought would be simple...I need help!

Still receive the same error even after converting to Whole Number and Text.

 

I told you, it seems simple but it just isn't working.

Vvelarde Super Contributor
Super Contributor

Re: What I Thought would be simple...I need help!

hi @mrcooper

 

can you post a screenshot of your column DaysPastDue showing part of his values and type of column.




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Sean Super Contributor
Super Contributor

Re: What I Thought would be simple...I need help!

@mrcooper Numbers are Right-justified in the Column while Text is Left-justified as in the picture

Notice in the first 2 - the numbers are on the right side inside the column

while in the 3rd they are on the left side in the Column meaning (Text not Number) and that is where the ERROR occurs

If you convert the column you are referencing in the AR Status formula to Data Type Number it should work!

 

2016-10-22 - SWITCH - DaysPastDue3.png

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)