Need help on DAX function with measure vs column

[ABC11]

Hello,

Lastest_WorkDate = CALCULATE(MAX(LEM_FACTS[WORKDATE]))-this is measure column

NO_OF_DAYS_Running Behind = DATEDIFF(LEM_FACTS[Lastest_WorkDate],TODAY(),DAY)-this is measure column

Level_1 =Switch( true(),

LEM_FACTS[NO_OF_DAYS_Running Behind]4,"GOOD",

LEM_FACTS[NO_OF_DAYS_Running Behind]<7,"Email Vendor", LEM_FACTS[NO_OF_DAYS_Running Behind]<21,"Action Immediately", LEM_FACTS[NO_OF_DAYS_Running Behind]<61,"Check to see if it's on site","Off Site")

This is working perfectly fine as measure column but when I do as add column its not.

I need to do column because I need to do graph as well.

Any help would be appriciate.

Thanks,

[SpartaBI]

Hey @ABC11 ,
Just wrtie this funcion in a new column (I've put everyhing in one column):
Level 1 = 
VAR _Lastest_WorkDate =  LEM_FACTS[WORKDATE]
VAR _Diff = DATEDIFF(Lastest_WorkDate,TODAY(),DAY)
VAR _Result =
switch( 
true(),

_Diff < 4,"GOOD",

_Diff<7,"Email Vendor",
_Diff<21,"Action Immediately",
_Diff<61,"Check to see if it's on site",
"Off Site")
RETURN
_Result


Hope this helps

[ABC11]

Hello,

I am getting this error" Failed to resolve name 'LEM_FACTS'. It is not a valid table, variable, or function name." My table bname is this for sure

Please

Thanks for your time

[SpartaBI]

Check now, had a typo, I edited it

[ABC11]

Thanks its worked.

but something is not working. Total I have 102 contract.  

[SpartaBI]

I need more info.

Pay attenation that the solution was for a calculated column. Check that the result is correct in the data tab view. You showes a screenshot of a visual which is an aggregation on top of what we did and it depends on the filter context generated by the visual and also on the summarization of the column ( i see count, maybe you wanted sum)

[ABC11]

I would like to count (no of vendor(CNRCNSUM)) who fall under those five categary. Total I have 102 CNRCNSUM. 

This report suppose to show me - each vendor(or contract no) submit their lastest workdate invocie(we call as LEM). So we calculated maximum workdate. then we calculated no of days running behind. and then five categaroy.

For Business summary - I would like to show how many vendor fall under which categary. 

Would you help me please

[SpartaBI]

I'm sorry

I need to see exactly the data, it's already deviated a lot from what I thought was the question. PM me if you want

@vojtechsima any ideas on what is missing from our replies so far?

[ABC11]

How can I share my data. How/where I can upload my file PBI file

[vojtechsima]

@ABC11 Hi, 
you can share your model as Screenshot of Model in Power BI Desktop and sample data by pasting a raw data here in comments like this:

Data	Data2	Something More
456	KDAa	Something Else
456853	DSA	Yes
913250	KDAa	No
1369647	DSA	Something Else
1826044	KDAa	Yes
2282441	DSA	No
2738838	KDAa	Something Else
3195235	DSA	Yes
3651632	KDAa	No
4108029	DSA	Something Else

Ideally, if you can upload your file to OneDrive (or Google Drive) and share the link, that would be awesome, if you have sensitive data, please re-create your file using random values, keep the structure and model and send it like this.

[ABC11]

SITEID	VENDOR_NAME	CNRCMSNUM	DESCRIPTION	Lastest_WorkDate	NO_OF_DAYS_Running Behind	Level
HORIZON	ABC	C0160722	Acden - Light Vehicle Fleet and HD Equipment Mtc - Horizon	4/13/2022 0:00	5	Email Vendor
HORIZON	XYZ	CTR0174983	Acden Facilities - HVAC Maintenance - Horizon	4/13/2022 0:00	5	Email Vendor
HORIZON	PQR	CTR0242927	Acuren - NDE, NDT, Rope Access - Horizon	4/14/2022 0:00	4	Email Vendor
HORIZON	A	CTR0109798	Acuren - NDE, NDT, Rope Access - Horizon - CLOSED USE CTR024292	1/30/2022 0:00	78	Off Site
HORIZON	B	823350-48	Air Chek - Master Engineering Services - Horizon	9/13/2021 0:00	217	Off Site
HORIZON	C	CTR015599	Air Liquide - Nitrogen Pumping Services - Horizon	9/29/2021 0:00	201	Off Site
HORIZON	D	CTR017966	Aker - Asset Integrity Services - Horizon	4/16/2022 0:00	2	GOOD
HORIZON	G	CTR019158	Altex - Exchanger Bundle Pulling - Horizon	4/12/2022 0:00	6	Email Vendor
HORIZON	H	CTR024121	ATI CANADA - MAINTENANCE LABOUR - HORIZON	3/22/2022 0:00	27	Check to see if it's on site
HORIZON	G	CTR010676	Belterra - Belt Installation, Mtc., Repair - Horizon	4/7/2022 0:00	11	Action Immediately
HORIZON	K	CTR016310	Black McDonald - Mechanical Maintenance - Horizon	4/6/2022 0:00	12	Action Immediately
HORIZON	L	CTR017182	Blackstone - Rotating Equipment Maintenance - Horizon	4/14/2022 0:00	4	Email Vendor
HORIZON	D	CTR013515	Bouchier Contracting - Roads, Grounds, Fire Water Mtc. - Horizon	4/15/2022 0:00	3	GOOD
HORIZON	T	CTR016519	Bouchier Site Services- Facility Maintenance - Horizon	4/16/2022 0:00	2	GOOD
HORIZON	C	CTR015845	Brock Canada - Scaffold Rentals - Horizon	2/26/2022 0:00	51	Check to see if it's on site
HORIZON	A1	CTR016786	CANOCO ENERGY - Maintenance - Welding & Pipefitting - Horizon	4/3/2022 0:00	15	Action Immediately
HORIZON	B2	CTR018007	Ceda - Dredging and Dewatering - Horizon	4/12/2022 0:00	6	Email Vendor
HORIZON	C3	CTR009028	Ceda - P&V Industrial Cleaning and Mechanical - Horizon	3/4/2022 0:00	45	Check to see if it's on site
HORIZON	G5	821089	Challenger - Survey Services - Horizon	3/24/2022 0:00	25	Check to see if it's on site

[ABC11]

Hello Vojtechsima,

does this data help you? do you want something more? please, let me know

Thanks

[SpartaBI]

Strange. Can you share your file here?

[vojtechsima]

Hi, @ABC11 
Could you please clarify more clearly why and what it's not working? I suppose I don't see the problem you're talking about.

[ABC11]

Thanks to Vojtechsima.

Everthing is working in measaure column. I need to do these as add column becuase I need to draw graph(pie) and do the summary report as %. In order to do that I need this calculation as column (not measure) 

Please, help me