Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

when select two stages in slicer and date, bring me the latest stage's date as a filtering for view

Suppose we have a slicer (stages) and user will select two stages and another slicer (completion date) for all work (jobs)

current logic ->

I selected "second stage" and "third stage" for STAGES slicer
I selected 12/1/2022 to 1/1/2023 in the completion date slicer

I will see only "third stage" because "second stage"s "completion date" is not between the "completion date" range
(SEE CIRCLE IN RED)

What business wants ->
They want to see both selected stages, and create a logic : look at the latest selected stage, if that latest selected stage's completion date within the slicer date range? bring the other selected stage regardless it's completion date

so I can calculate the variance days between these two
(SEE CIRCLE IN GREEN)
1-12-2023q1.png

9 REPLIES 9
v-xinruzhu-msft
Community Support
Community Support

Hi @Anonymous 

You can create a column to rank in table:

Column = SWITCH([STAGES],"FirstStage",1,"SecondStage",2,"ThirdStage",3,"FifthStage",4,"ForthStage",5,"SixthStage",6)

Then create a new table to put it to slicer:

Table 3 = SUMMARIZE('Table',[STAGES],[Column])

Create a new measure:

Measure = var a=MAXX(ALLSELECTED('Table'),[Column])
var b=MAXX(FILTER(ALLSELECTED('Table'),[Column]=a),[Completion date])
return IF(ISFILTERED('Table 3')=FALSE(),SUM('Table'[Completion date]),IF(b>=MINX(ALLSELECTED('Table 2'),[Date])&&b<=MAXX(ALLSELECTED('Table 2'[Date]),[Date]),SUM('Table'[Completion date]),BLANK()))

And put it to the visual filter:

vxinruzhumsft_0-1673587790859.png

 

Then create a new measure to calculate sub:

Measure 2 = CALCULATE(DATEDIFF(MIN([Completion date]),MAX([Completion date]),DAY),FILTER(ALLSELECTED('Table'),[Measure]<>BLANK()))

Output:

vxinruzhumsft_1-1673587845385.png

 

vxinruzhumsft_2-1673587920222.png

Best Regards!

Yolo Zhu

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

 

 

Anonymous
Not applicable

and one more thing, that would cause a problem... I mentioned stages as in order, but what if there were just names not in order 1st, 2nd, 3rd ? that would bring a different solution ? One thing we are sure that if there are 10 different stages they are in historical order. Completion date's are in order

Anonymous
Not applicable

Hey ! 

This looks great ! but I need a favor from you to use it on my system. We have AAS Tabular model which brings scripts from SSMS to create table in visual studio and using the measures and columns in there to display in power bi. Meaning, I don't have any privilage to create a table in power bi

In order to bring a new table (columns) to power bi, I should create them in the tabular model (visual studio). So, is it okay if I just create a table with no relationship (pk or fk ) with any other table in the tabular model ? Do you know about that ?

It's my mistake, I should've told here in the beginning that I use AAS Tabular model for power bi. 

Hi @Anonymous 

The column can be created in measure, and you can use Import mode to import the data, then you can create the column in table.

Best Regards!

 

Yolo Zhu

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

 

Anonymous
Not applicable

How about this question I asked earlier: 

"and one more thing, that would cause a problem... I mentioned stages as in order, but what if there were just names not in order 1st, 2nd, 3rd ? that would bring a different solution ? One thing we are sure that if there are 10 different stages they are in historical order. Completion date's are in order"

what if user selects stages that we can not order ? stage x and stage y ?
but we DO KNOW one all stage's completion date are in historical order.
Meaning y is completed ALWAYS after x completion.

How can we solve that ?

Hi @Anonymous 

You can refer to the following measure:

Test = 
 var _min_date = MIN('Table 2'[Date])
 var _max_date =  MAX('Table 2'[Date])
 var _t = ADDCOLUMNS(ALLSELECTED('Table') , "Flag" ,IF( 'Table'[Completion date]>= _min_date && 'Table'[Completion date] <= _max_date  , 1 , 0))
 return
IF( SUMX(_t,[Flag])>=1 ,MAX('Table'[Completion date]))

and you just need to create this measure, and don't need to create other calculated column or table.

Output:

vxinruzhumsft_0-1673947973117.png

 

vxinruzhumsft_1-1673947990267.png

Best Regards!

Yolo Zhu

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

 

Anonymous
Not applicable

Flag is working properly except this situation :

Bilhan_0-1673981401630.png

Here we have a date slicer (12/1/2022 - 1/1/2023) and we have two stage selected 

(thirdstage and forthstage) filter should be depending on the latest selected stage

meaning if forthstage is withing the date range, bring me earlier selected stage.

But, here forthstage is out of the range. It should be the other way.

amitchandak
Super User
Super User

@Anonymous , You need use an independent date table for that , Once you have that have meausre like

 

Measure =

var _min1 = minx(allselected(Date),Date[Date])

var _max = maxx(allselected(Date),Date[Date])

var _min= maxx(filter(Table, Table[completion Date] <_min1), Table[completion Date])

return

countrows( filter('Table', 'Table'[Date] <=_max && 'Table'[Date] >=_min ))

 

variance

variance=

var _min1 = minx(allselected(Date),Date[Date])

var _max1 = maxx(allselected(Date),Date[Date])

var _min= maxx(filter(Table, Table[completion Date] <_min1), Table[completion Date])

var _max= maxx(filter(Table, Table[completion Date] <=_max1), Table[completion Date])

return

datediff(_min, _max,day)+1

Anonymous
Not applicable

we have two stages to be selected, and the latest selected stage's completion date should be considered, and even though earlier selected stage's completion date is not within the date range, it should be on the table view. I'm sorry but I don't understand your explanation at all.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors