Update Status Runtime on filter

[Anonymous]

Requirement:

I need the correct status for each item, both for a single prodid and for multiple prodids. I want to accumulate the same items along with their quantities and update their status accordingly.

Restriction:

I must achieve this using a calculated column and cannot use Measures or calculated tables.

Problem:

I've successfully accumulated the quantities of repeated items for multiple selected prodids. However, I'm encountering difficulties in preparing the status column. Despite trying various approaches, I haven't achieved the desired results.

Location:

You can find the details in the second tab under 'Ordering Status Dashboard Consolidated View'.

I have attached the .pbiz file and some screenshots to provide a better understanding.

In this link you can finf the pbiviz file:

https://drive.google.com/drive/folders/1yGN8Qq_VXUvYQ5dsblUbXrxS_XkTDEfC?usp=sharing

Your positive response would be highly appreciated. Thanks

 

Spoiler

 

 

 

 

[v-zhangti]

Hi, @Anonymous 

Can you give me an example of your current state results that do not meet your expected output and what the correct output should be?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-zhangti 

Thank you for your response. Unfortunately, due to our server's restrictions within the organization, I'm unable to explain from server.

Allow me to illustrate the situation using an example:

I've created a measure named "StatusColumnMeasure," which displays correct results for both single production selection and multiple selections. I also have calculated column, named 'Statuses' I also have attached the pictures below

My requirement is to have this status information presented in a column format, not as a measure or calculated table, as I need to use this status information in charts as well.

The issue arises when comparing the status of itemid = P100269 in the 1st. For the single production order, the column displays "OH/Need to Order," while the measure shows "OH/Need to Order," which is the correct status.
Wherease when comparing the status of itemid = P100269 in the 2nd. For the multiple production order, the column displays "OH/Need to Order," That's wrong result. while the measure shows "OH," which is the correct status.


Note: You can also view the status query to determine the correct status. I'm creating the different status on different QTYs. Let me write the it:


If REQ quantity is equal to OH quantity then status is OH, If REQ quantity is equal to NTO quantity then status is NTO, If REQ quantity is equal to Ordered quantity then status is Ordered, If REQ quantity is equal to OH quantity + Ordered quantity then status is OH/Ordered, If REQ quantity is equal to OH quantity + NTO quantity then status is OH/NTO, If REQ quantity is equal to NTO quantity + Ordered quantity then status is Partially Ordered. 

Let me write Status Measure and Columsn queries. . 
    

StatusColumnMeasure =

SWITCH (

    TRUE (),

    [ReqQty] = [NToQty] && [OHQty] = 0 && [OrdQty] = 0, "Need to Order",

    [ReqQty] = [OHQty] && [NToQty] = 0 && [OrdQty] = 0, "OH",

    [ReqQty] = [OrdQty] && [OHQty] = 0 && [NToQty] = 0, "Ordered",

    [ReqQty] = [OHQty] + [OrdQty] && [NToQty] = 0 && [OHQty] <> 0 && [OrdQty] <> 0, "OH/Ordered",

    [ReqQty] = [OHQty] + [NToQty] && [OrdQty] = 0 && [OHQty] <> 0 && [NToQty] <> 0, "OH/Need to Order",

    "Partially Ordered"

)

Statuses =

IF(PSInventShortAccReportTable[ReqQTYSUM] = PSInventShortAccReportTable[NTOQTYSUM] && PSInventShortAccReportTable[OHQTYSUM] = 0 && PSInventShortAccReportTable[OrdQTYSUM] = 0, "Need to Order",

if( PSInventShortAccReportTable[ReqQTYSUM] = PSInventShortAccReportTable[OHQTYSUM] && PSInventShortAccReportTable[NTOQTYSUM] = 0 && PSInventShortAccReportTable[OrdQTYSUM] = 0, "OH",

IF(PSInventShortAccReportTable[ReqQTYSUM] = PSInventShortAccReportTable[OrdQTYSUM] && PSInventShortAccReportTable[OHQTYSUM] = 0 && PSInventShortAccReportTable[NTOQTYSUM] = 0, "Ordered",

IF(PSInventShortAccReportTable[ReqQTYSUM] = PSInventShortAccReportTable[OHQTYSUM] + PSInventShortAccReportTable[OrdQTYSUM] && PSInventShortAccReportTable[NTOQTYSUM] = 0 && PSInventShortAccReportTable[OHQTYSUM] <> 0 && PSInventShortAccReportTable[OrdQTYSUM] <> 0, "OH/Ordered",  

IF(PSInventShortAccReportTable[ReqQTYSUM] = PSInventShortAccReportTable[OHQTYSUM] + PSInventShortAccReportTable[NTOQTYSUM] && PSInventShortAccReportTable[OrdQTYSUM] = 0 && PSInventShortAccReportTable[OHQTYSUM] <> 0 && PSInventShortAccReportTable[NTOQTYSUM] <> 0, "OH/Need to Order",

"Partially Ordered"

))))

)

I have pasted this just understand the status which is depedent on QTYs.