hi, I thought this will be easy but I am stuck with this.I have OTD (OnTimeDelivery) as status. By defaullt,NOT YET RECEIVED = Received Date nullNO = Requested Date null or Received Date > Requested DateYES = Received Date <= Requested Date Now, we want the OTD to be dynamic based on what if parameterWhat if parameter = days difference of received date and requested date.For example,What if Parameter: (must use between)-10 +5 This means, all orders with days difference from -10 up to +5 days will be yes.the rest will still show as no or not yet received. Below is my sample data.Doc NoLine NoRequested DateReceived DateDAYS DIFFOTDOTD Index19-0000410000null1/3/2019nullNO219-00006100001/3/20191/3/20190YES119-00007100001/3/20191/3/20190YES119-00011100001/3/20191/6/20193NO219-00011200001/3/20191/6/20193NO219-00011300001/3/20191/6/20193NO219-00011350001/3/20191/6/20193NO219-00011375001/3/20191/6/20193NO219-00021100001/6/20191/8/20192NO219-00021200001/6/20191/8/20192NO219-00021300001/6/20191/8/20192NO219-00021400001/6/20191/8/20192NO219-00021500001/6/20191/8/20192NO219-00021600001/6/20191/8/20192NO219-00021700001/6/20191/8/20192NO219-00021800001/6/20191/8/20192NO219-00029100001/7/20191/8/20191NO219-00029200001/7/20191/8/20191NO219-00029300001/7/20191/8/20191NO219-00034100001/8/20191/8/20190YES119-00036100001/8/20191/8/20190YES119-00037100001/10/20191/13/20193NO219-00037200001/10/20191/13/20193NO219-00038100001/10/20191/13/20193NO219-00038200001/10/20191/13/20193NO219-00046100001/9/20191/9/20190YES119-00059100001/12/20191/12/20190YES119-00059200001/12/20191/12/20190YES119-00078100001/13/20191/24/201911NO219-00082100001/13/20191/13/20190YES119-00082200001/13/20191/13/20190YES119-00082300001/13/20191/13/20190YES119-00082400001/13/20191/13/20190YES119-00082500001/13/20191/13/20190YES119-00088100001/14/20191/16/20192NO219-00089100001/14/20191/16/20192NO219-00090100001/14/20191/14/20190YES119-00090200001/14/20191/14/20190YES119-00090300001/14/20191/14/20190YES119-00090400001/14/20191/14/20190YES119-00090500001/14/20191/14/20190YES119-00090600001/14/20191/14/20190YES119-0009110000null1/14/2019nullNO219-0009120000null1/14/2019nullNO219-0009130000null1/14/2019nullNO219-00097100001/15/20191/24/20199NO219-0013610000null1/24/2019nullNO219-0013620000null1/24/2019nullNO219-00137100001/20/20191/20/20190YES119-00137200001/20/20191/20/20190YES119-00137300001/20/20191/20/20190YES119-00138100001/21/20191/20/2019-1YES119-00138200001/21/20191/20/2019-1YES119-00138300001/21/20191/20/2019-1YES119-0013910000null1/20/2019nullNO219-0013920000null1/20/2019nullNO219-0013930000null1/20/2019nullNO219-0013940000null1/20/2019nullNO219-0013950000null1/20/2019nullNO219-00198100001/24/20191/24/20190YES119-0019820000null1/24/2019nullNO219-0019830000null1/24/2019nullNO219-01158800004/8/20194/9/20191NO219-013001300004/17/20194/17/20190YES119-013031300004/17/20194/17/20190YES119-01387100005/2/20195/19/201917NO219-01756100005/27/2019null21NOT YET RECEIVED219-02512100007/22/2019null23NOT YET RECEIVED219-02792300008/6/2019null0NOT YET RECEIVED1

mussaenda - Create another table with status. StatusNot yet recievedYesNo Then create another DAX measure StatusCount = var _status= CALCULATE(SELECTEDVALUE('Status'[Status])) var _gp= CALCULATETABLE(SUMMARIZE(SampleData,SampleData[Doc No],"status",[_OTD Status])) var _count= CALCULATE(COUNTROWS(FILTER(_gp,[status]=_status))) return _countAdd status column into the legend field and add [StatusCount] measure into the value field. Please mark this solution as accepted, if you find this solution useful. Regards,Nandu Krishna

mussaenda

Community Champion

6 years ago

Solved

Status based on What if Parameter

hi,

I thought this will be easy but I am stuck with this.

I have OTD (OnTimeDelivery) as status.

By defaullt,

NOT YET RECEIVED = Received Date null

NO = Requested Date null or Received Date > Requested Date

YES = Received Date <= Requested Date

Now, we want the OTD to be dynamic based on what if parameter

What if parameter = days difference of received date and requested date.

For example,

What if Parameter: (must use between)

-10 +5

This means, all orders with days difference from -10 up to +5 days will be yes.

the rest will still show as no or not yet received.

Below is my sample data.

Doc No	Line No	Requested Date	Received Date	DAYS DIFF	OTD	OTD Index
19-00004	10000	null	1/3/2019	null	NO	2
19-00006	10000	1/3/2019	1/3/2019	0	YES	1
19-00007	10000	1/3/2019	1/3/2019	0	YES	1
19-00011	10000	1/3/2019	1/6/2019	3	NO	2
19-00011	20000	1/3/2019	1/6/2019	3	NO	2
19-00011	30000	1/3/2019	1/6/2019	3	NO	2
19-00011	35000	1/3/2019	1/6/2019	3	NO	2
19-00011	37500	1/3/2019	1/6/2019	3	NO	2
19-00021	10000	1/6/2019	1/8/2019	2	NO	2
19-00021	20000	1/6/2019	1/8/2019	2	NO	2
19-00021	30000	1/6/2019	1/8/2019	2	NO	2
19-00021	40000	1/6/2019	1/8/2019	2	NO	2
19-00021	50000	1/6/2019	1/8/2019	2	NO	2
19-00021	60000	1/6/2019	1/8/2019	2	NO	2
19-00021	70000	1/6/2019	1/8/2019	2	NO	2
19-00021	80000	1/6/2019	1/8/2019	2	NO	2
19-00029	10000	1/7/2019	1/8/2019	1	NO	2
19-00029	20000	1/7/2019	1/8/2019	1	NO	2
19-00029	30000	1/7/2019	1/8/2019	1	NO	2
19-00034	10000	1/8/2019	1/8/2019	0	YES	1
19-00036	10000	1/8/2019	1/8/2019	0	YES	1
19-00037	10000	1/10/2019	1/13/2019	3	NO	2
19-00037	20000	1/10/2019	1/13/2019	3	NO	2
19-00038	10000	1/10/2019	1/13/2019	3	NO	2
19-00038	20000	1/10/2019	1/13/2019	3	NO	2
19-00046	10000	1/9/2019	1/9/2019	0	YES	1
19-00059	10000	1/12/2019	1/12/2019	0	YES	1
19-00059	20000	1/12/2019	1/12/2019	0	YES	1
19-00078	10000	1/13/2019	1/24/2019	11	NO	2
19-00082	10000	1/13/2019	1/13/2019	0	YES	1
19-00082	20000	1/13/2019	1/13/2019	0	YES	1
19-00082	30000	1/13/2019	1/13/2019	0	YES	1
19-00082	40000	1/13/2019	1/13/2019	0	YES	1
19-00082	50000	1/13/2019	1/13/2019	0	YES	1
19-00088	10000	1/14/2019	1/16/2019	2	NO	2
19-00089	10000	1/14/2019	1/16/2019	2	NO	2
19-00090	10000	1/14/2019	1/14/2019	0	YES	1
19-00090	20000	1/14/2019	1/14/2019	0	YES	1
19-00090	30000	1/14/2019	1/14/2019	0	YES	1
19-00090	40000	1/14/2019	1/14/2019	0	YES	1
19-00090	50000	1/14/2019	1/14/2019	0	YES	1
19-00090	60000	1/14/2019	1/14/2019	0	YES	1
19-00091	10000	null	1/14/2019	null	NO	2
19-00091	20000	null	1/14/2019	null	NO	2
19-00091	30000	null	1/14/2019	null	NO	2
19-00097	10000	1/15/2019	1/24/2019	9	NO	2
19-00136	10000	null	1/24/2019	null	NO	2
19-00136	20000	null	1/24/2019	null	NO	2
19-00137	10000	1/20/2019	1/20/2019	0	YES	1
19-00137	20000	1/20/2019	1/20/2019	0	YES	1
19-00137	30000	1/20/2019	1/20/2019	0	YES	1
19-00138	10000	1/21/2019	1/20/2019	-1	YES	1
19-00138	20000	1/21/2019	1/20/2019	-1	YES	1
19-00138	30000	1/21/2019	1/20/2019	-1	YES	1
19-00139	10000	null	1/20/2019	null	NO	2
19-00139	20000	null	1/20/2019	null	NO	2
19-00139	30000	null	1/20/2019	null	NO	2
19-00139	40000	null	1/20/2019	null	NO	2
19-00139	50000	null	1/20/2019	null	NO	2
19-00198	10000	1/24/2019	1/24/2019	0	YES	1
19-00198	20000	null	1/24/2019	null	NO	2
19-00198	30000	null	1/24/2019	null	NO	2
19-01158	80000	4/8/2019	4/9/2019	1	NO	2
19-01300	130000	4/17/2019	4/17/2019	0	YES	1
19-01303	130000	4/17/2019	4/17/2019	0	YES	1
19-01387	10000	5/2/2019	5/19/2019	17	NO	2
19-01756	10000	5/27/2019	null	21	NOT YET RECEIVED	2
19-02512	10000	7/22/2019	null	23	NOT YET RECEIVED	2
19-02792	30000	8/6/2019	null	0	NOT YET RECEIVED	1

Need Help

Show and Tell

nandukrishnavs
6 years ago
mussaenda - Create another table with status.

Status
Not yet recieved
Yes
No

Then create another DAX measure
StatusCount = var _status= CALCULATE(SELECTEDVALUE('Status'[Status])) var _gp= CALCULATETABLE(SUMMARIZE(SampleData,SampleData[Doc No],"status",[_OTD Status])) var _count= CALCULATE(COUNTROWS(FILTER(_gp,[status]=_status))) return _count
Add status column into the legend field and add [StatusCount] measure into the value field.

Please mark this solution as accepted, if you find this solution useful.

Regards,
Nandu Krishna

3 Replies

nandukrishnavs

Community Champion

6 years ago

mussaenda - Try this measure

_OTD Status = 
VAR minVal =
    CALCULATE ( MIN ( 'days difference'[days difference] ) )
VAR maxVal =
    CALCULATE ( MAX ( 'days difference'[days difference] ) )
VAR reqDate =
    CALCULATE ( SELECTEDVALUE ( SampleData[Requested Date] ) )
VAR resDate =
    CALCULATE ( SELECTEDVALUE ( SampleData[Received Date] ) )
VAR daysDiff =
    CALCULATE ( DATEDIFF ( reqDate, resDate, DAY ) )
VAR result =
    IF (
        ISBLANK ( resDate ),
        "Not yet recieved",
        IF ( daysDiff >= minVal && daysDiff <= maxVal, "Yes", "No" )
    )
RETURN
    result

You can find the sample pbix file here https://drive.google.com/open?id=1EbTM2ktkNhWehRxsL55Qyg6njQ4LP1NM

mussaenda
Community Champion
6 years ago
Thnak you nandukrishnavs ,

but do you have any idea why it is not allowed on a pie chart?

Thank you
- nandukrishnavs
  Community Champion
  6 years ago
  mussaenda - Create another table with status.
  
  Status
  Not yet recieved
  Yes
  No
  
  Then create another DAX measure
  StatusCount = var _status= CALCULATE(SELECTEDVALUE('Status'[Status])) var _gp= CALCULATETABLE(SUMMARIZE(SampleData,SampleData[Doc No],"status",[_OTD Status])) var _count= CALCULATE(COUNTROWS(FILTER(_gp,[status]=_status))) return _count
  Add status column into the legend field and add [StatusCount] measure into the value field.
  
  Please mark this solution as accepted, if you find this solution useful.
  
  Regards,
  Nandu Krishna

Forum Discussion

Status based on What if Parameter

3 Replies

Related content

Projection based on numeric parameters

Parameter in summarize

Dynamic Field Parameter base on another Parameter

How to filter a slicer dynamically based on selection on field parameter

notebook parameter