Filter data by date selecting a label

[fdsilva]

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?

[MFelix]

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]

@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]

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]

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]

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]

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.

[MFelix · Accepted solution]

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]

Hi @MFelix ,

That's it! You are the man!

After I inserted another invoice, the filter worked like a charm. However, 1) only works properly with two or more invoices. With only one invoice, nothing happens. And 2) I had to put the date field in all the charts that I'm using (doughnut, matrix, columns...) too. Without the date field, the charts appear blank when filtering. This could be a problem depending on the kind of chart that is being used.

Well, if you have some tip for these two points, please, don't be shy. haha! Otherwise, I could survive with them.

Once again, thank you so much!

[MFelix]

Hi @fdsilva ,

Testing on my file everything works properly, if I select only 1 invoice it show the resources for that date, if I do a donut or bar chart without dates it also works.

Is your measure like I place it or did you make any adjustemnets?

Regards,

MFelix

[fdsilva]

Hi @MFelix ,

Yeah, everything works properly if I select one invoice. But for this, I need to have more than one invoice in the filter field. If I have only one registered, nothing happens.

Regarding the requirement of the date, I don't know why but it worked only with it in the visual field. This is not a problem for me, for now, because I'm using a matrix, a donut, and a bar chart. The date information is shown only whether I make a drill-down in the chart. The default visualization looks ok.

The measure is the same that you posted, with no one adjustment. Take a look below.

Filter Invoice = 
VAR MinimumDate = MAX(Invoices[StartDate])
VAR MaximumDate = MAX(Invoices[EndDate])
RETURN
IF (
    COUNT(Invoices[number]) = CALCULATE(COUNT(Invoices[number]); ALL(Invoices[number]));
    1;
    IF (
        COUNTROWS(ALLSELECTED(Invoices)) < 1;
        BLANK();
        IF (
            MAX(BillingRecords[Date]) <= MaximumDate
                && MAX(BillingRecords[Date]) >= MinimumDate;
            1;
            BLANK()
        )
    )
)

[MFelix]

Hi @fdsilva,

Regarding the filter option is because what the measure is doing is checking if the selection on the slicer is equal to all invoices numve so if you slicer only have 1 option the value is false so it does not return values.

The second part of your question not sure of what is happening in my example is working properly.

If that is not impacting the report in order not to have your report users making wrong drill downs disable the headers options on each visual especially the drill down buttons.

Regards
MFelix

[fdsilva]

Hi @MFelix ,

Thank you for the explanation. I'll think about something to fix this case when exists only one invoice.

Disable the header options is a good idea. I'll do that for the donut and bar charts. To the matrix I need of the drill down.