Using a measure as a filter on a matrix

[Rbie]

Hi all,

I have a table with the following columns:

ValidFrom,ValidTo,Department,Personnelnumber,Name
I have a slicer on date from a datetable
I created filter measure to be able to filter on the validfrom/valditodate like,

InDateRange =

Var _TheDate = FIRSTDATE(FilterDateTable[Date])

return

if( SELECTEDVALUE(WorkForce[ValidFrom]) <= _TheDate

&&

SELECTEDVALUE(WorkForce[ValidTo]) >= _TheDate

,1,0)
This filter works if I use a table or all fields in a matrix , but when I try to create a matrix based on department it goes wrong.
What I'm I missing?

Thanks in advance,
Rbie

[Anonymous · Accepted solution]

Hi, @Rbie 

You can put the following measure to matrix directly to solve your need. There are 63 lines that are eligible.

MEASURE = 
VAR _firstDate =
    FIRSTDATE ( FilterDateTable[Date] )
VAR _counts =
    CALCULATE (
        COUNTROWS ( Sheet1 ),
        FILTER (
            Sheet1,
            Sheet1[ValidFrom] <= _firstDate
                && Sheet1[Validto] >= _firstDate
        )
    )
RETURN
    _counts

 
By the way, there are some problems with your method, in the case of only the Department column, the corresponding date value cannot be found normally with the Selectvalue function, there are multiple values in the ValidTo of SHE and SHOP&SITES, the lookup will fail, it will be displayed as 0 in Indaterange, and these rows do not participate in the calculation when calculating the number of rows.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Rbie]

Thank you very much, obviously I have a lot to learn 🙂

[Anonymous]

Hi, @Rbie 

Thanks for Ritaf1983 reply. Can successfully open your file, but what you want to achieve the final effect you can give the effect of it, did not understand your ultimate needs.

Best Regards,
Yang
Community Support Team

[Rbie]

Hi,

My goal is to use the filter and aggregate on department, this doens't work. The first image is the aggregate without using the filter, the second is with the date filter they should be the same in this case, but they aren't. If I add the personnelnumber to the second then the filter does work, it doesn't when I aggregate.

[Rbie]

Hi Ritaf1983,

Here is the link with a test pbix with data.

https://we.tl/t-zEaPWxQqIl 

[Ritaf1983]

Hi @Rbie 

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly