Help needed to refresh a table after a slicer is selected

[Surajb]

Hey All,

This is my first message and might miss something. Please correct me if I missed any information to provide.

I am trying to run a report where in I need to find a product that is in progress based on the time slicer that I select.

For example, please refer the following table:

Product_id	Status	StatusDatetime
1	Draft	1-Jun-19
1	InProgress	2-Jun-19
1	Approved	30-Jun-19
2	Draft	2-Jun-19
2	InProgress	2-Jun-19
2	Approved	3-Jun-19

Based on the time slicer selected (which is a datetime column), I need to check if the product was in progress status, i.e. I need to check the last status for each product in the given date range and show if it was in progress.

If I select a date slicer from 01-Jun-2019 to 02-jun-2019 I should get

----------------------------------------------------------------------
1 InProgress
2 InProgress

If I select a date slicer from 03-Jun-2019 to 30-jun-2019 I should get
----------------------------------------------------------------------
1 InProgress

If I select a date slicer from 01-Jun-2019 to 03-jun-2019 I should get
----------------------------------------------------------------------
1 InProgress

----------------------------------------------------------------------
If I select a date slicer from 01-Jun-2019 to 30-jun-2019 I should get

0 records

I read in the community posts that a calculated measure/calculated tables help solve similar problem, but this case is slightly different. Every time a time slicer is selected, the last status of a product in that date range can change. Basically all I can think of is having a table which gets calculated everytime a slicer is selected which I think is not support by power BI as of now. Any help help would be highly appreciated 🙂

[Surajb]

Hi All, I have the following data : 

Created By	Product_id	Status	StatusDatetime
Suk	10	New	1-Jun-19
Suk	10	InProgress	2-Jun-19
Suk	10	Approved	30-Jun-19
Suk	11	New	2-Jun-19
Suk	11	InProgress	2-Jun-19
Suk	11	Approved	3-Jun-19
Suk	13	New	2-Jun-19
Min	15	New	8-Jun-19
Min	16	New	11-jun-19
Min	16	InProgress	12-Jun-19
Min	16	Approved	13-Jun-19
Min	1	New	01-May-19

I need the following Matrix table which gives me count of product aggregated by created BY and their counts for New , Approved an in Progress Products.

Created By	New	In Progress	Approved
Suk	3	1	2
Min	2	1	1

Date Slicer  : 01-Jun-2019 to 30-Jun-2019

In this matrix New and Approved can be found in the slicer but in Progress may or may not be available in the slicer.

What I am trying to achive is in a given time Slice, we want to know how many products were created, how many were in progress and how many got approved.

If a product got created before the time slice, and is still not approved in the time slice, then we need to show it as in progress.

In the above sample, Suk had one in progress as one product Product_id = 13 was in progress as it was created on 2nd Jun 2019  and was not approved in the time slice

Min had one InProgress as one product Product_id = 1 was created on 1st May-2019 and was not approved. Note that this record wont be available in the time slicer ..But I need to count this as well.

Please let me know if this can be achieved. New and approved count are actions done on the product during the time slice and in Progress is the final status of the product.

Please help !

Thanks in advance.

[Surajb]

Until now I didnt find a solution for this. If anyone have come across such situation please do comment.

Thanks 

[v-xuding-msft]

Hi @Surajb ,

I created a measure you can have a try.

Measure = var a = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Status    ] = "InProgress"))
var b = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Status    ] = "Approved"))
return
IF(ISBLANK(b) && NOT(ISBLANK(a)),"InProgress",BLANK())

Best Regards,

Xue Ding

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

[Surajb]

Hi @v-xuding-msft ,

This does help but is it possible to filter this record compeltely instead of setting it as blank ? I have a matrix which refers this table and I would like to filter those records from the table which is currently set to blank.

Once again thanks for your kind inputs. 

[Surajb]

No one ? Its kind of urgent 😞 😞  I really need help on this. Any help would be highly appreciated!