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
govi
Helper II
Helper II

Matrix with fixed number of columns based on date

Hi all,

I want to create a matrix with a fixed number of columns (31)

 

In a table I have Subjects, they all have a startdate, which is different for all subjects.

When I filter the matrix visual for Subject "X", the startdate for this subject has to populate the first column(name) of the matrix, the second column(name) has to be the startdate +1, etc till column 31.

 

Is this possible?

 

Thank you,

govi

1 ACCEPTED SOLUTION

Hi, @govi 

 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

 

Subscriptions:

d2.png

 

Alerts:

d1.png

 

AlertRange(a calculated table):

AlertRange = GENERATESERIES(1,31,1)

 

Relationship:

d3.png

 

You may create a calculated column and a measure as below.

 

Calculated column:
AlertDays = 
var _startdate = LOOKUPVALUE(Subscriptions[StartDate],Subscriptions[ID],[ID])
return DATEDIFF(_startdate,[AlertDate],DAY)+1

Measure:
Result = 
COUNTROWS(
    FILTER(
        Alerts,
        Alerts[AlertDays]=SELECTEDVALUE(AlertRange[Value])
    )
)

 

Result:

d4.png

 

d5.png

 

 

Best Regards

Allan

 

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

View solution in original post

6 REPLIES 6
lbendlin
Super User
Super User

you haven't said anything about the rows, or the values.  Or what you actually want to achieve.

Hi @lbendlin ,

You are right, let me explain more:

I have these tables:

govi_0-1594480096411.png

"Subscriptions" lists all IDs with their startdate.

The startdate is day 1 of 31

For 31 days the subjects will answer questions, some anwers will generate alerts.

The alerts are listed in table "Alerts"

 

Lets's focus on "id003":

"id003" has "Alert 1" on 13-7 (which is the second day he is participating)

He also has "Alert 3" twice on 14-7 (third day participating)

 

I would like to generate a matrix visual that shows all alerts for a specific subject and when they happened.

See example (for "id003") below:

govi_1-1594480461400.png

Is this possible?

 

Thanks!

govi

 

Hi, @govi 

 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

 

Subscriptions:

d2.png

 

Alerts:

d1.png

 

AlertRange(a calculated table):

AlertRange = GENERATESERIES(1,31,1)

 

Relationship:

d3.png

 

You may create a calculated column and a measure as below.

 

Calculated column:
AlertDays = 
var _startdate = LOOKUPVALUE(Subscriptions[StartDate],Subscriptions[ID],[ID])
return DATEDIFF(_startdate,[AlertDate],DAY)+1

Measure:
Result = 
COUNTROWS(
    FILTER(
        Alerts,
        Alerts[AlertDays]=SELECTEDVALUE(AlertRange[Value])
    )
)

 

Result:

d4.png

 

d5.png

 

 

Best Regards

Allan

 

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

Hi @v-alq-msft @TomMartens @lbendlin 

 

Thanks a lot for you help!

 

I used the solution v-ald-msft provided.

 

I worked!

 

Thanks all!

 

@govi

Hey @govi ,

 

I created a table with numeric values ranging from 1 to 31 using this DAX statement, GENERATESERIES creates a column name automatically named Value, it's easy to rename, but I assume your data model already has a table like this.

Alertindex = 
GENERATESERIES(1 , 31 , 1)

Then I created a calculated column in the alerts table that creates an index, based on the start date (make sure that already a relationship exists between the subscriptions table and the alerts table. Here is the DAX statement for the calculated column:

AlertIndex = 
DATEDIFF(RELATED('Subscriptions'[Datestart]) , 'Alerts'[AlertDate] , DAY) + 1

 I created a relationship between the new column and the table, the data model looks like this:

TomMartens_0-1594487286573.png

This allows creating a report like this:

TomMartens_2-1594487388506.png

Hopefully, this is what you are looking for.

 

Regards,

Tom

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

In the Alerts table add a calculated column that lists the difference between the alert date and start date for each subscription and alert. Then you can put that calculated column in the values area of your matrix and the alert name in the row area.

 

The only thing still left to do is to handle days without alerts.  You can use "show items with no data"  for that, but for that to work you need to have another dimension table that simply lists from 1 to 31. Put that in the columns area, and link the table to the alerts.

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.