Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
BramSegers
Frequent Visitor

Matrix with dynamic rows?

I've got a very small dataset (too small to post 😊) that contains just 3 tables: 

Vehicles which contains two columns: Vehicle No. & Standard Department

VehicleStandard Department
DTR-465COIL-ACE
DTR-466COIL-ACE
DTR-467COIL-ACE
DTR-468ACE-ATT
DTR-469ACE-ATT
DTR-470IBERICA


Appointed Department which contains 4 columns: Vehicle No., From Date, Till Date, Appointed Department

VehicleFromTillAppointed Department
DTR-46501/01/202031/01/2020ACE-ATT
DTR-46501/02/202028/02/2020IBERICA
DTR-47001/01/2020 COIL-ACE

 

Turnover which contains 2 columns: Vehicle No., Turnover. 

VehicleTurnover
DTR-465€ 200
DTR-466€ 300
DTR-467€ 250
DTR-468€ 275
DTR-469€ 315
DTR-470€ 180

 

What i want to achieve is to create a matrix table and give the end user the possibilty to filter on a date, filtering my matrixtable immedately with the current correct info.

 

So if the user selects till 30/01/2020 he would get something like:

 Selection:   Selection: 
 30/01/2020   03/02/2020 
       
  Turnover   Turnover
ACE-ATT-790 ACE-ATT-590
 DTR-465200  DTR-468275
 DTR-468275  DTR-469315
 DTR-469315 COIL-ACE-930
COIL-ACE-730  DTR-466300
 DTR-466300  DTR-467250
 DTR-467250  DTR-470180
 DTR-470180  DTR-465200

 

Any suggestions how to achieve this?

11 REPLIES 11
sreenathv
Solution Sage
Solution Sage

You may try the following solution. It is not an optimum solution, but a simple one to start with.

 

Step 1:

Add a calendar table to your data model. Select "New Table" and use the following DAX code.

 

Calendar = CALENDAR(DATE(2020,1,1),DATE(2020,2,29))

 

Change the date ranges as per your requirement. 

Step 2:

Add another calculated table to your data model. Select "New Table" and use the following DAX code.

 

VehicleCalendar = CROSSJOIN(Vehicles,'Calendar')

 

Step 3:

Add two calculated columns to the "VehicleCalendar" table that you have added in Step 2.

 

 

Appointed Department =
CONCATENATEX (
    FILTER (
        'Appointed Department',
        AND (
            AND (
                'Appointed Department'[From] <= VehicleCalendar[Date],
                'Appointed Department'[TillDate] >= VehicleCalendar[Date]
            ),
            'Appointed Department'[Vehicle] = VehicleCalendar[Vehicle]
        )
    ),
    'Appointed Department'[Appointed Department]
)
Relevant Department =
IF (
    ISBLANK ( VehicleCalendar[Appointed Department] ),
    VehicleCalendar[Standard Department],
    VehicleCalendar[Appointed Department]
)

 

Step 3a: Add another calculated column to the "Appointed Department" table.

TillDate = IF(ISBLANK('Appointed Department'[Till]),MAX('Calendar'[Date]),'Appointed Department'[Till])

Step 4: Add a measure

 

Turnover = SUMX(Turnover,Turnover[Turnover])

 

 

The relationships should be as follows...

 

Relationships.png

 

Please pay attention to the Cardinality and Cross Filter Directions of the relationships

 

Step 5:

Add a date slicer to your data model using the "Date" field from the "Calendar" table.

 

Step 6:

Add a matrix visual and add the "Relevant Department" and "Vehicle" Columns from the table named "Vehicle Calendar" into the Rows of the matrix visual.

 

Step 7:

Add the measure named "Turnover " that we have created in Step 4 to the "Values" section of the matrix visual.

 

This will give the results as shown in my earlier post.

 

This may not be the most optimum solution, but once you do this, you will get started and you can further improvise.

Thanks a lot for this help, really appreciated!!! 

The one thing left is that we only have to look at the "Till" ("Tot") date to determine the "Department Appointed".

So for the below scenario, since 14/02/2020 is in the latest period, only IBERICA should be visible. Is this something we can achieve as well?

2020-03-08 13_26_32-Alders-TEST - Power BI Desktop.png

If you only need to select the till date only, then you can just change the date selector to "Drop Down" instead of "Between" as shown in the images below. Then set the "Single Select" property of the date slicer to true.

 

CalendarSingleDateSelection.png

 

CalendarSingleSelect.png

 

 

First of all, thanks again for the reply.

That would indeed work, but as I said it's a simplified dataset. The actual dataset has a calculated turnover for the selected period as well and should appear on the department the vehicle belongs to on the end of the selected period. 

 

 

I am not able to understand your problem. Whatever solution I have posted is based on the table structures and sample data you have posted here. It is okay that you post a few dummy data, and the volume of your actual data is huge. But all the table structures and the relationships, basically your data model, should be the same as what you have posted here. Only then the solution will work. 

 

Hi,

 

It's in fact not more then a split up of the turnover table: 

 

VehicleTurnoverDate
DTR-465€ 7517/12/2019
DTR-465€ 2504/01/2020
DTR-465€ 3016/01/2020
DTR-465€ 7015/02/2020
DTR-466€ 30004/02/2020
DTR-467€ 25006/02/2020
DTR-468€ 27508/02/2020
DTR-469€ 31509/02/2020
DTR-470€ 18003/02/2020

 

So if the end user want to select period: 01/12/2019 - 04/03/2020, it should appear as: 

DTR-465 - COIL-ACE - € 200 (because no appointed department at 04/03/2020). 

 

If he selects 01/12/2019 - 18/02/2020 though, it should appear as:

DTR-465 - IBERICA - € 200 

 

So far so good, but when he selects 01/12/2019 - 03/02/2020, it should be: 

DTR-465 - IBERICA - € 130, because the calculated sum over turnover is just €130 on 03/02/2020.

 

Got it or do you want me to create more dummy data with full structure, which is furthermore irrelevant to this issue?

Ashish_Mathur
Super User
Super User

Hi,

I do not understand your question.  Vehicles DTR-466,467,468 and 469 have no record at all in the second table.  Therefore, when one selects the date as 30/1/2020, why should these appear in the final result?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi,

Thanks for your reply.

I indeed didn't mention that, but those vehicles should appear date-independant, because they have a registered turnover.

Refer to the table,

 

VehicleFromTillAppointed Department
DTR-46501/01/202031/01/2020ACE-ATT
DTR-46501/02/202028/02/2020IBERICA
DTR-47001/01/2020 COIL-ACE

Here vehicle no DTR-465 is appointed to IBERICA department during the period from 01/02/2020 to 28/02/2020. Then why is vehicle no. DTR-465 appearing in department "COIL-ACE" in your example when the selected date is 03/02/2020?

 

I need clarification on this to arrive at the logic. Because when I tried the solution, following are the outputs I am getting in Power BI with date selections 30/01/2020 and 03/02/2020.

 

Example30012020.pngExample03022020.png

 

 

You are totaly correct, my deepest apologies. 😱

It should appear on the IBERICA department indeed.

amitchandak
Super User
Super User

@Ashish_Mathur , would you be able to help on this?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.