Fail to Add another "OOS" Condition

[Chanleakna123]

hi , i posted this as the second time as my question had been delaid and no further response for 10 days . 

Hi All, 

* I have 2 tables , Table 1 is : Code , and table 2 as table above 

I linked the relationship between these 2 tables. 

i have condition as calculated as per above , and i set " Normal , Expired , High Risk as per above " , But i wanna add another "OOS" mean if the Code in Table 2 didn't appear  , it will show "OOS " while i use the Table 1 as the main , and visualize the condition in Table 2.  Because the Code in table 2 didn't appear , mean there's no QTY. 

how to success this ? 

I experienced with multiples condtion , but failed to get this . 

Here is my Table "Code " I called it dimension table 

Here is my "Master Data table " I called it Fact Table , my Master Data table also has Custom Column is Condition which i showed above and below 

[PattemManohar]

@Chanleakna123 It will be great if you can post the sample data of the two tables (Copiable format) and expected output as well. It will help to understand the scenario clearly and to resolve the issue quicker.

[Chanleakna123]

hi @PattemManohar , i just edited my post , can you help me urgently on this 😞

[PattemManohar]

@Chanleakna123 Thanks !! But I can't copy data from an image format. Could you please post the sample data in copiable format, also expected output as well.

[Chanleakna123]

@PattemManohar

if 1105 are missing in My Fact Table , then it will show "OOS" which this "OOS" will be related with "High Risk " , Normal , POS , i want their filter to be together. 

This is my DOI " Dimension Table "

Code

Description

DOD

Day selflive

Categories

HIGH Risk

POS Risk

High Stock

1108	Coke 1.5 L 6 KH	14	180	PET 1.5L	60	60	90
2824	DASANI STILL 1.5L 6 S/W NP KH	14	365	PET 1.5L	90	90	180
4295	Coke 600ml PET12 4x6	14	120	PET 600ml	45	45	75
1105	Mutant Red 330ml Sleek can	56	365	CAN 330ml	90	90	180

Master Date Table , "Fact Table "

Plant

Current Date

Code

Description

Categories

MGF Date

#Pallets

#Qty CS/Pallat

#Case

H051	17-Oct-18	4295	Coke 600ml 4*6 S/W NP	PET 600ml	15-Sep-18	25	54	12
H051	17-Oct-18	2824	Dasani 1.5L	PET 1.5ml	20-Sep-18	3	112	41
H051	17-Oct-18	1108	Coke 1.5 L 6 KH	PET 1.5ml	26-Sep-18	5	80	50

[PattemManohar]

@Chanleakna123 As per you initial post, try to have another condition (which will be first condition) as below:

Here is the draft logic you are looking for... You might need a tweak for this to have other conditions as well, the below will give OOS if there is no matching value found.

Column = IF(LOOKUPVALUE('01Fact'[Code],'01Fact'[Code],'01DimCode'[Code]) <> BLANK(),"Exists","OOS")

[Chanleakna123]

@PattemManohar can we have the OOS condition included with the condition i made ? 

[PattemManohar]

@Chanleakna123 As you are doing nested if and deriving the "Condition Risk" based on the value found on other table, if there is no value found it will have nothing. So add an another field to simplify the logic... there you can check as below

Condition Risk New = IF([Condition Risk] = BLANK(), "OOS",[Condition Risk])

Hope this makes sense !!

[Chanleakna123]

hi @PattemManohar

below is my Condition Risk , i tried using urs, it doesn't appear OOS on the Filter. mean only Normal , POS , Expired. no OOS appear. can you please tutorial this ? Sorry for inconvenient 

Condition Risk = IF('Master Data'[Expired in (Days)]>RELATED(DOI[High Stock ] ),"Normal", IF('Master Data'[Expired in (Days)]<=RELATED(DOI[HIGH Risk ]),"High Risk",IF('Master Data'[Expired in (Days)]>RELATED(DOI[POS Risk]) && 'Master Data'[Expired in (Days)]<=RELATED(DOI[High Stock ]),"Potential Expired")))

[PattemManohar]

@Chanleakna123 Just want to be clear, it means you are using this derived field "Condition Risk" as a slicer on the report. There you are not able to see the "OOS" value. But are you able to see the "OOS" value in the table after changing the logic as suggested.

[Chanleakna123]

hi @PattemManohar the condition risk will be a slicer , Normal , POS , High Risk and OOS , 

if i click on OOS , it will show the Code number which we have not put in our Master Data Table call Fact Table , 

We will visualize the DOI Table Called Dimension Table , As Code , 

Therefore , if we click OOS on any day , it will show only the code we haven't key in in Master Data , 
If the other day we fill in that Code , it will run to NOrmal POS or High Risk , 

So OOS here can be had or non based on the code Number on Master Data Table we fill compared to DOI Code Table 

[Chanleakna123]

@PattemManohar do you have any answers on this  ? 

[PattemManohar]

@Chanleakna123 I am not very clear about what you are looking for in this scenario. Based on my understanding, you want to identify the "Codes" that are present in Fact table but not present in Dimension Table. This you are trying to achieve through a calculated field called "Condition Risk" which contains values Normal, High Risk, OOS. 

My question is - If you create a calculated column as mentioned earlier, using nested if then you have the values Normal,High Risk and OOS. Then why can't you use this field as filter (Slicer) and then drag the code from Fact table (Table in which you have created the calculated field i.e. "Condition Risk") then it should solve your problem. 

Please let me know if I am missing anything here.....

[Chanleakna123]

@PattemManohar hi , 

due to below Calcuation is from the Fact table which contain Expired In ( Days ) , and then i compare to other as dimension table called DOI to linked with the #Days whether those in High Risk , Normal or POS , But i fail to Get OOS even i use fact table to get it .

and my condition need to add another "OOS" , i tried it , and it fails.

 Condition Risk = IF('Master Data'[Expired in (Days)]>RELATED(DOI[High Stock ]  ),"Normal", IF('Master Data'[Expired in (Days)]<=RELATED(DOI[HIGH Risk ]),"High Risk",IF('Master Data'[Expired in (Days)]>RELATED(DOI[POS Risk]) && 'Master Data'[Expired in (Days)]<=RELATED(DOI[High Stock ]),"Potential Expired")))

https://drive.google.com/file/d/1QItiIu86rOKtDEK8kwv_RtzikrZZYegS/view?usp=sharing

yoou can try with the link above , my slicer wanna have OOS included . when the Master Data has no Code compare to DOI ,show OOS . can you please drop your comment 

[Chanleakna123]

hi @PattemManohar in addition , 

this will be filter by date , What wee want is : if my Master Data Table " Fact Table " doesn't have the Code number the same as "DOI Table " called Dimension Table , this will OOS , 

it's like today there's OOS on this Product , but on the other day , if the code match there will be showing whether It's Normal or POS or High Risk ,