Forum Discussion

mostho's avatar
mostho
Icon for Helper II rankHelper II
2 years ago
Solved

Right measure for a matrix visual

I have a table FT with 3 columns:
- DateA

- DateB

- Val
I have a matrix visual with DateB as Column, DateA as Row and sum of Val as values. works so far.

Now i wanna add a date slicer (with some given Dates e.g, from a disconnected table with one column DateC) which should filter the records from FT such that DateA > DateC and DateB < DateC

 

Example for FT:

DateADateBVal
20-01-202415-12-2023100
24-12-202307-12-2023200
25-01-202407-01-2024150
13-02-202420-11-2023180
23-12-202317-11-2023190


If I would select 31-12-2023 in the slicer the result should be:

 20-11-202307-01-2024Total
25-01-2024 150150
13-02-2024180 180
Total180150330


Additional Info: DateA and DateB are not unique. The combination of DateA and DateB also does not have to be unique.

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi mostho 

     

    First of all, I need to confirm your requirements with you. According to the condition that DateA > DateC and DateB < DateC, the output image should not be 07-01-2024.

     

    The following testing is for your reference.

     

    Create a measure as follows

    Measure = IF(SELECTEDVALUE('Table'[DateA]) > SELECTEDVALUE('DateC'[Date]) && SELECTEDVALUE('Table'[DateB]) < SELECTEDVALUE('DateC'[Date]), 1, 0)

     

    Put the measure into the visual-level filters, set up show items when the value is 1.

     

    Output:

     

    Best Regards,
    Yulia Xu

     

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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi mostho 

     

    First of all, I need to confirm your requirements with you. According to the condition that DateA > DateC and DateB < DateC, the output image should not be 07-01-2024.

     

    The following testing is for your reference.

     

    Create a measure as follows

    Measure = IF(SELECTEDVALUE('Table'[DateA]) > SELECTEDVALUE('DateC'[Date]) && SELECTEDVALUE('Table'[DateB]) < SELECTEDVALUE('DateC'[Date]), 1, 0)

     

    Put the measure into the visual-level filters, set up show items when the value is 1.

     

    Output:

     

    Best Regards,
    Yulia Xu

     

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

    • mostho's avatar
      mostho
      Icon for Helper II rankHelper II
      2 years ago

      Anonymousyes, youre right, i had the wrong record in the result.

      thank you very much for the solution, i was very close but now i know, what my problem of understanding was.