Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
@v-jiascu-msft your answer to this question helped me a lot but I still have one problem. My example is a little bit different. Instead of quarters I want to append a column with user id who was absent on specfied day:
date | user id
1.1.2017 | 1
2.1.2017 | 1
3.1.2017 | 2
...
I have a table of users absence:
start_date | end_date | user_id
1.1.2017 2.1.2017 1
3.1.2017 3.1.2017 2
...
Your example works perfect if there is only one user absent on one day. But muliple users can be absent on one day. So in that case I need the table to be something like (user 1 and 2 both absent on 2.1.2017):
date | user id
1.1.2017 | 1
2.1.2017 | 1
2.1.2017 | 2
3.1.2017 |
...
Can you or anyone else help?
Solved! Go to Solution.
I managed to solve my problem with a solution given here:
http://community.powerbi.com/t5/Desktop/Dynamically-generate-table-rows-for-each-row/m-p/101483#M425...
HI @Silko
How about adding a Calculated Column in your Main Table stating if the User was PRESENT or ABSENT on that day
Then you can filter only those who were ABSENT
Present or Absent = VAR Check = CALCULATE ( COUNTROWS ( AbsenceTable ), FILTER ( AbsenceTable, AbsenceTable[User ID] = MainTable[User ID] && AbsenceTable[Start Date] <= MainTable[Date] && AbsenceTable[End Date] >= MainTable[Date] ) ) RETURN IF ( Check >= 1, "Absent", "Present" )
I'm not sure you got my question right. I'm talking only about absent users and my absence table looks like this:
start_date | end_date | user_id (absent_user_id)
1.1.2017 3.1.2017 1
3.1.2017 3.1.2017 2
I need it to look like this:
date | absent_user_id
1.1.2017 | 1
2.1.2017 | 1
3.1.2017 | 1
3.1.2017 | 2
...
My apologies.
I thought you have 2 Tables.
One with Dates and Users.
And other one ... the absence Table
So you want to TRANsform your Absence table into that FORMAT... Right?
Hi @Silko
Good to see this Power Query Solution
Here is a DAX solution
Go to Modelling Tab>>>New Table
NEW TABLE = GENERATE ( ALL ( AbsenceTable[User ID] ), GENERATESERIES ( CALCULATETABLE ( VALUES ( AbsenceTable[Start Date] ) ), CALCULATETABLE ( VALUES ( AbsenceTable[End Date] ) ) ) )
I managed to solve my problem with a solution given here:
http://community.powerbi.com/t5/Desktop/Dynamically-generate-table-rows-for-each-row/m-p/101483#M425...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |