Forum Discussion

HeihoSilver's avatar
HeihoSilver
Frequent Visitor
2 years ago

Measure used in Histogram does not filter Table

Hi, 
I have a Data Table called "Request" consists of ID, Location and Due Date columns. I would like to create a visualization of "histogram" and a "table". 
The histogram should consists of X-axis which based on Location and Y-axis which is a count of ID based on the Date less than Today.  Here is the measure for Y-Axis.

_ID_Delayed = 
CALCULATE(COUNTA(Requests[ID]),  (TODAY()) > Requests[Due Date])

 In the "Table" visual I added columns ID, Location and Due Date. 
When I click one of the bar (based on Location) in histogram, it does not filter the "Table" correctly. It shows all the Due Date instead of Due Date less than Today. 
Is there a way to achieve this?
Here is the picture for example: When click the histogram on location "Aasta", it should only show 1 record in the table based on the Due Date less than today. However, the table shows all Due Date belonging to Aasta.

 

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    HI HeihoSilver,

    You can add an if statement to package the expression and redirect not match range to blank. Then power bi will auto hide this blank part to achieve the filter effects:

    _ID_Delayed =
VAR currDate =
    MAX ( Requests[Due Date] )
RETURN
    IF (
        currDate < TODAY (),
        CALCULATE ( COUNTA ( Requests[ID] ), Requests[Due Date] < TODAY () )
    )

    Regards,

    Xiaoxin Sheng

    • HeihoSilver's avatar
      HeihoSilver
      Frequent Visitor
      2 years ago

      Hi, 

      Thank you for the input, but it didn't provide the result I want. 
      By using your code, it returns only 1 result with the latest due date before today. The table return correct result though (show 1  result) after clicking the bar.
      However, there are many Locations with Due Date less than today (not only the latest before Today). Is there another way?

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        HI HeihoSilver,

        If your date is less than today, you can add a variable to extract the last date from the current tab with current filter effects and use this variable as condition in if statement and expression calculations.

        Regards,

        Xiaoxin Sheng