Forum Discussion

StatStories_Lon's avatar
StatStories_Lon
Helper I
6 years ago
Solved

Filter Measure on Date, created by summarize date

Dear all,   My name is Ruud and I posted last week a topic in which I had a problem. That problem was solved to a certain extent and I have put the topic to solved. I could not un-solve the topic, ...
Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    6 years ago

    Hi StatStories_Lon ,

     

    Sorry, I may have misunderstood you.

    Please replace the filter condition "all" of the measure with "ALLSELECTED".

     

    Measure = 
VAR _tbl = SUMMARIZE(Dim_Contract, Dim_Contract[ContractId], Dim_Users[UserId], Dim_Contract[Start_Date], Dim_Contract[End_Date], "Qtde Hours",
CALCULATE(SUM('Fact_Target Hours'[Target Hours]), FILTER(ALLSELECTED('Fact_Target Hours'),
'Fact_Target Hours'[medewerker_id] = EARLIER(Dim_Users[UserId]) &&
'Fact_Target Hours'[Datum] >= EARLIER(Dim_Contract[Start_Date]) &&
'Fact_Target Hours'[Datum] <= EARLIER(Dim_Contract[End_Date]) &&
'Fact_Target Hours'[Datum] <= TODAY()
))
)
RETURN SUMX(_tbl, [Qtde Hours])

     

    What really puzzles me is here

     

     

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

StatStories_Lon's avatar
StatStories_Lon
Helper I
6 years ago

Thank you for your message.

 

Try to create a measure using the if statement and apply it to the visual level filter.

Measure 2 = IF(MAX(Dim_Date[Date])>=MAX(Dim_Contract[Start_Date])&&MAX(Dim_Date[Date])<=TODAY(),1)

 

I tried this, however, when I set measure2 as a visual filter I could not change its values. Whereas when you can change it it turns gray, in mine it is still white.  See the picture below

 

Cannot change the measure2 filter value.

V-lianl-msft's avatar
V-lianl-msft
Community Support
6 years ago

Hi StatStories_Lon ,

 

Sorry, I may have misunderstood you.

Please replace the filter condition "all" of the measure with "ALLSELECTED".

 

Measure = 
VAR _tbl = SUMMARIZE(Dim_Contract, Dim_Contract[ContractId], Dim_Users[UserId], Dim_Contract[Start_Date], Dim_Contract[End_Date], "Qtde Hours",
CALCULATE(SUM('Fact_Target Hours'[Target Hours]), FILTER(ALLSELECTED('Fact_Target Hours'),
'Fact_Target Hours'[medewerker_id] = EARLIER(Dim_Users[UserId]) &&
'Fact_Target Hours'[Datum] >= EARLIER(Dim_Contract[Start_Date]) &&
'Fact_Target Hours'[Datum] <= EARLIER(Dim_Contract[End_Date]) &&
'Fact_Target Hours'[Datum] <= TODAY()
))
)
RETURN SUMX(_tbl, [Qtde Hours])

 

What really puzzles me is here

 

 

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

  • StatStories_Lon's avatar
    StatStories_Lon
    Helper I
    6 years ago

    Dear Liang,

     

    Thank you so much! Your suggestion was the solution that I was looking for.

    Regarding the contractsituation for person A4: this is probably a mistake made that I overlooked when wanting to upload the sample dataset.

     

    Best,

    Ruud