cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Matrix with fixed number of columns based on date

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
Highlighted
Super User V
Super User V

Re: Matrix with fixed number of columns based on date

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

Highlighted
Helper II
Helper II

Re: Matrix with fixed number of columns based on date

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

 

Highlighted
Super User V
Super User V

Re: Matrix with fixed number of columns based on date

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.

Highlighted
Super User IV
Super User IV

Re: Matrix with fixed number of columns based on date

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
Highlighted
Community Support
Community Support

Re: Matrix with fixed number of columns based on date

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

Highlighted
Helper II
Helper II

Re: Matrix with fixed number of columns based on date

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

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors