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 th...
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