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: Invoi...
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

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

  • fdsilva's avatar
    fdsilva
    Frequent Visitor
    7 years ago

    Hi MFelix ,

     

    Thank you again!

     

    So close to the solution, man... but the fact that there can not be an out-of-period type is a problem. That way will not work for me. **bleep**.

    I would like this invoice filter worked like the date slicer that I put below it, just filtering by date.

     

    Do you have any other idea?

     

    My plan B is to create a new column in the Resources table and update the records with the corresponding invoice number, row by row. That way I could make a relationship between Invoice and Resources. But I need to create this routine and, in my mind, this is not the best approach. In some situations, this could go wrong...

     

    Thank you in advance.

    • MFelix's avatar
      MFelix
      Super User
      7 years ago

      Hi fdsilva ,

       

      Redo your measure to the following should work as expected although in my data is working is two small to confirm full result.

       

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

      Regards,

      MFelix

      • fdsilva's avatar
        fdsilva
        Frequent Visitor
        7 years ago

        Hi MFelix ,

         

        Uhu, almost there!

         

        I made a modification in your DAX to show all resources when having no invoices selected.

         

        Filter Invoice = 
VAR MinimumDate =
    MAX ( Invoices[StartDate] )
VAR MaximumDate =
    MAX ( Invoices[EndDate] )
RETURN
    IF (
        HASONEVALUE(Invoices[InvoiceNr]);
        IF (
            MAX(Resources[ConsuptionDate]) <= MaximumDate
                && MAX(Resources[ConsuptionDate]) >= MinimumDate;
            1;
            BLANK()
        );
        1
    )

        This worked for the sample PBIX that we are working, but not in my real data. And I don't know why.

         

        The same way, in my real data, I had to put the date column in the visual component as well. Without it, the filtering goes crazy and brings me just a little portion of the data. With the date information, the data has filtered correctly. However, as I mentioned above, even when I have no one invoice selected, the data is filtered as if I had selected one. The measure filter option is still set up to "Not blank".

         

        Tomorrow morning (is 11pm here in Brazil now) I'll register a new invoice to see what happens and to try to understand what is going on.

         

        If you have some clue to show all records when no one invoice is selected, please, tell me.

         

        Thank you so much.