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.
Hi,
I need help to create a measure for the following example :
Imagine I have this table (called Tickets).
Number | Opened date | Closed date |
T1 | 01-janv | 01-janv |
T2 | 01-janv | 02-janv |
T3 | 02-janv | 04-janv |
T4 | 03-janv | 03-janv |
I would like to create a measure [Nb_closed_by_opened_date] that count the number of tickets (countrows) closed at a particular date provided by a pivot table filter based on the Opened date field.
The result I would expect with this measure woule be a pivot table like :
Opened date | Nb_closed_by_opened_date |
01-janv | 1 |
02-janv | 2 |
03-janv | 0 |
Can anywone help me ?
Thanks a lot !
Solved! Go to Solution.
@OwenAuger : Thanks for sharing the Relationship link. I tried the problem mentioned and it works well.
@oberthou : Please refer the description and screen shot attached
Number_count_closed_date = CALCULATE(COUNTA(Sheet2[Number]),USERELATIONSHIP(Sheet2[Closed date],Table_1[Opened date]))
Table_1 = SUMMARIZE(Sheet2,Sheet2[Opened date])
Steps are as follows : -
Create a new table with Open Data as mentioned above "Table_1" and a Calculated Column as "Number_count_closed_date".
Create relationship between closed data and open date , it will be (* : 1) relationship, make this active relationship
Create open date and Number_count_closed_date relationship , it will be inactive
create a table from Table_1[Opened date] and Sheet2[Number_count_closed_date]
Go to "Visual level filter" and uncheck blank to remove count of 04th Jan , as this date is NOT in Opened Date Column
Go to "Values" section and check "Show items with no data"
Make sure when you uplaod data , both the column are of Date . I ahve change the format as dd/month/yyyy . you can choose as required
Please see below the screen shot
Hope it helps. Regards
@oberthou : The problem is to count closed date rows based on filter applied on opened date.
Measure would look like this :
Measure_CLosed_Date = CALCULATE(COUNTROWS(Table),ALLEXCEPT(Table,Table[Opened date]))
Can you please confirm , if this is what you are looking for. Thanks
Hi Mridul,
Thanks a lot but it is not what I expect. I want to count the number by closed dates but on the pivot have this based on the the open date filter by row. Let me better explain. I also made a mistake in my exemple also ... it should look like :
the correct initial Table is actually :
ber | Opened date | Closed date |
T1 | 01-janv | 01-janv |
T2 | 01-janv | 02-janv |
T3 | 02-janv | 02-janv |
T4 | 03-janv | 04-janv |
I kept your measure in the pivot table below I renamed as Nb Opened.
But the one I need is the other column "Nb_closed_by_open_date"
Nb_opened = CALCULATE(COUNTROWS(Table),ALLEXCEPT(Table,Table[Opened date]))
Nb_closed_by_open_date = ? that's what I need actually ! ...
Opened date | Nb_opened | Nb_closed_by_opened_date |
01-janv | 2 | 1 |
02-janv | 2 | 2 |
03-janv | 0 | 0 |
Indeed, 01-Jan there was only 1 ticket closed (T1) and on 02-Jan there was 2 tickets closed (T2 and T3)
None were closed on 03-Jan.
Hope this is clearer ?
Thanks.
Have a look at this post:
Multiple Relationships Between Tables in DAX
In your situation you probably want an active relationship between Opened Date and your Date table, and an inactive relationship between Closed Date and your Date table.
@OwenAuger : Thanks for sharing the Relationship link. I tried the problem mentioned and it works well.
@oberthou : Please refer the description and screen shot attached
Number_count_closed_date = CALCULATE(COUNTA(Sheet2[Number]),USERELATIONSHIP(Sheet2[Closed date],Table_1[Opened date]))
Table_1 = SUMMARIZE(Sheet2,Sheet2[Opened date])
Steps are as follows : -
Create a new table with Open Data as mentioned above "Table_1" and a Calculated Column as "Number_count_closed_date".
Create relationship between closed data and open date , it will be (* : 1) relationship, make this active relationship
Create open date and Number_count_closed_date relationship , it will be inactive
create a table from Table_1[Opened date] and Sheet2[Number_count_closed_date]
Go to "Visual level filter" and uncheck blank to remove count of 04th Jan , as this date is NOT in Opened Date Column
Go to "Values" section and check "Show items with no data"
Make sure when you uplaod data , both the column are of Date . I ahve change the format as dd/month/yyyy . you can choose as required
Please see below the screen shot
Hope it helps. Regards
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |