Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
Hi, @govi
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Subscriptions:
Alerts:
AlertRange(a calculated table):
AlertRange = GENERATESERIES(1,31,1)
Relationship:
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:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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:
"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:
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:
Alerts:
AlertRange(a calculated table):
AlertRange = GENERATESERIES(1,31,1)
Relationship:
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:
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:
This allows creating a report like this:
Hopefully, this is what you are looking for.
Regards,
Tom
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.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |