topic Data grouping based on second table in DAX Commands and Tips

Data grouping based on second table

Mike1309 — Thu, 14 Dec 2023 13:29:11 GMT

Dear Experts,

I'm looking for a solution to a problem I cannot deal with

I have two tables: Periods:

and invoices:

every period for every country is different

Based on country and invoice date I would like to know to which period from the "period" table the invoice belongs to

I think I need your help as I have no idea how to deal with

the result should be:

if the user chooses the country and period should get a list of invoices

Thank you for your help in advance

Mike

Re: Data grouping based on second table

Tom_Y — Thu, 14 Dec 2023 21:37:03 GMT

All you need is a "Date Table". Search google/ youtube. It's not very difficult.

1. Make a Date Table.

2. Link your 2 tables to that Date Table.

3. Add a Slicer to your matrix, something look like this one, allowing users to choose "Period" and "Country".

Re: Data grouping based on second table

Anonymous — Fri, 15 Dec 2023 08:02:24 GMT

Hi Mike1309

I have understood about your problem, here is the solution I offer:

Here are the data of the two tables, you can check the date type, here it is recommended to use the (m/d/yyyy) format

Create a measure, you can match the period by judging the country name and date range.

result = var invoiceDate = SELECTEDVALUE(invoices[invoice date]) var invoiceCountry = SELECTEDVALUE(invoices[country]) RETURN CALCULATE( MAX(periods[period]), FILTER(periods, periods[country] = invoiceCountry && periods[start day] <= invoiceDate && periods[end day] >= invoiceDate))

Here is the result, and you can add slicers to filter

Best Regards,

Nono Chen

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