Forum Discussion
Data Type in column (Text vs Whole Number)
Hi there,
I have 3 columns and I have the following formulas for each in PowerBI Table View:
Column 1: Column 1 = IF(('Sharepoint List'[Completed Date]-'Sharepoint List'[Date reported])> 0, CONVERT(NETWORKDAYS('Sharepoint List'[Date reported],'Sharepoint List'[Completed Date]),STRING),"WIP")
Column 2: Column 2 = IF(ISBLANK('Sharepoint List'[Completed Date]),"WIP",IF('Sharepoint List'[SLA Baseline]>='Sharepoint List'[Column 1],"Yes", "No"))
Column 3: SLA Baseline column
*For Column 1, I want it's Data Type to be "Whole Number" but because of the "WIP" wording in the false part of the formula, I have to change the data type of the column to "Text". Is there another way to do this while keeping it as a whole number column?
*For Column 2, I want this to be a Text column since "Yes", "No" and "WIP" are the entries I want visible. However, when all 3 columns (Column 1, Column 2, and SLA Baseline) are all Text fields, I get no error and I get a result BUT it's incorrect because this column cannot tell which is greater (Column 1 or SLA Baseline) since they're text fields instead of whole number.
*Also, when I change the SLA baseline Column from Text to Whole number or vice versa it works well so this is good!
2 Replies
- Greg_Deckler
Community Champion
PowerBinewbie19 Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.- PowerBinewbie19New Member
Hi Greg,
Thanks!
For Sample Data, please see below. For Column 3 (SLA Baseline), I would like the expected output to be "Yes", "No"
For Context, the Sharepoint List'[Completed Date] and Sharepoint List'[Date Reported] are BOTH Date data type not data/time.