Forum Discussion

Rbie's avatar
Rbie
Frequent Visitor
1 year ago
Solved

Using a measure as a filter on a matrix

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



Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      Rbie
      Frequent Visitor
      1 year ago

      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.



  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      Rbie
      Frequent Visitor
      1 year ago

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