Forum Discussion

fdsilva's avatar
fdsilva
Frequent Visitor
7 years ago
Solved

Filter data by date selecting a label

Hi folks!

 

I have a data table with information like ResourceName, ResourceType, Cost, ConsumptionDate, and others. Let's call it Table01.

Now, I created an invoice table, with the columns: Invoice Number, IssueDate, Cost, StartDate and EndDate. Let's call this one Table02.

In my visual report, I have a table showing the Table01 data and a slicer showing the invoices number.

 

What I'm trying to do is to filter the Table01 data by the date range of the invoice number selected in the slicer.

 

For instance: If I select the invoice number 966458, the table showing Table01 should be filtered by the corresponding date range. In this example 12/01/2019 to 20/01/2019. Is it possible?

 

Need Help
  • MFelix's avatar
    MFelix
    7 years ago

    Hi fdsilva ,

     

    Try the following code:

    Filter Invoice =
VAR MinimumDate =
    MAX ( Invoices[StartDate] )
VAR maximumdate =
    MAX ( Invoices[EndDate] )
RETURN
    IF (
        COUNT ( Invoices[InvoiceNr] )
            = CALCULATE ( COUNT ( Invoices[InvoiceNr] ); ALL ( Invoices[InvoiceNr] ) );
        1;
        IF (
            COUNTROWS ( ALLSELECTED ( Invoices ) ) < 1;
            BLANK ();
            IF (
                MAX ( Resources[ConsuptionDate] ) <= maximumdate
                    && MAX ( Resources[ConsuptionDate] ) >= MinimumDate;
                1;
                BLANK ()
            )
        )
    )

    Regards,

    MFelix

12 Replies

  • MFelix's avatar
    MFelix
    Super User
    7 years ago

    Hi fdsilva ,

     

    You need to create a filter measure to use on the resource visual:

    Filter Invoice =
VAR MinimumDate =
    MAX ( Invoices[StartDate] )
VAR maximumdate =
    MAX ( Invoices[EndDate] )
RETURN
    IF (
        COUNTROWS ( ALLSELECTED ( Invoices ) ) < 1;
        BLANK ();
        IF (
            VALUES ( Resources[ConsuptionDate] ) <= maximumdate
                && VALUES ( Resources[ConsuptionDate] ) >= MinimumDate;
            1;
            BLANK ()
        )
    )

    Then add this on the filters and select option not blank I'm assuming that you want to select only one invoice otherwise the resource table will be empty:

     

    Check PBIX file attach.

     

    Regards,

    MFelix

    • fdsilva's avatar
      fdsilva
      Frequent Visitor
      7 years ago

      MFelix, thank you very much for your reply. Your solution almost resolved my problem.

       

      In my scenario:

      • The Resources table has duplicate rows, except by the date/time field that has a difference in the time portion. I tested in the PBIX that you sent and is not a problem.
      • The date column (ConsumptionDate) is not shown/used.
      • The records are grouped by Type. This is why I can't use the date field.

      When I remove the ConsumptionDate from the table values, I got an error that says "Calculation error in measurement 'Invoices'[Filter Invoice]: A multi-valued table was provided, where a single value was expected."

       

      I edited your PBIX and save it with the error that I mention above. I just don't know how to put it here, like you did. hahaha.

       

      Edit: I saw that I can't upload files for now. Follow the link to download: Download PBIX

      • MFelix's avatar
        MFelix
        Super User
        7 years ago

        Hi fdsilva ,

         

        The question is related with the fact you have more than one date for the consuption you need to redo the measure with SELECTEDVALUES instead of VALUE so your measure will look like:

         

        Filter Invoice =
VAR MinimumDate =
    MAX ( Invoices[StartDate] )
VAR maximumdate =
    MAX ( Invoices[EndDate] )
RETURN
    IF (
        COUNTROWS ( ALLSELECTED ( Invoices ) ) < 1;
        BLANK ();
        IF (
            SELECTEDVALUE ( Resources[ConsuptionDate] ) <= maximumdate
                && SELECTEDVALUE ( Resources[ConsuptionDate] ) >= MinimumDate;
            1;
            BLANK ()
        )
    )

        Be aware that since you have a grouping by Type when one of the type have a consuption date different from the max or end date that resource will not appear in your filter.

         

        PBIX file changed attach.

         

        Regards,

        MFelix