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.
Hello everyone,
I've the follow table with summarized data, but it's working fine with a table, but I want to show the column Open_Tickets by Calendar in x-axis visualization, this is possible?
This is my DAX code:
@HugoJesus
Try this code please:
Total_Open_Tickets =
VAR TempTable =
SUMMARIZE (
Date_Link,
Date_Link[Calendar],
"Created_Tickets",
CALCULATE (
COUNT ( Date_Link[id] ),
FILTER ( Date_Link, Date_Link[Date_Type] = "Creation" )
),
"Closed_Tickets",
CALCULATE (
COUNT ( Date_Link[id] ),
FILTER ( Date_Link, Date_Link[Date_Type] = "Closure" )
),
"Daily_Open_Tickets",
CALCULATE (
COUNT ( Date_Link[id] ),
FILTER ( Date_Link, Date_Link[Date_Type] = "Creation" )
)
- CALCULATE (
COUNT ( Date_Link[id] ),
FILTER ( Date_Link, Date_Link[Date_Type] = "Closure" )
)
)
VAR LastDay =
MAX ( Date_Link[Calendar] )
VAR TempTable2 =
ADDCOLUMNS (
TempTable,
"Open_Tickets",
VAR _date = [Calendar]
RETURN
SUMX ( FILTER ( TempTable, [Calendar] <= _date ), [Daily_Open_Tickets] )
)
RETURN
ADDCOLUMNS(
TempTable2,
"Cummulative",
SUMX( FILTER(TempTable2, Date_Link[Calendar] <= EARLIER(Date_Link[Calendar])), [Daily_Open_Tickets])
)
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@HugoJesus
Do you still need the OPEN_TICKETS column? If not, change the code after the RETURN as below and try.
ADDCOLUMNS(
TempTable,
"Cummulative",
SUMX( FILTER(TempTable, Date_Link[Calendar] <= EARLIER(Date_Link[Calendar])), [Daily_Open_Tickets])
)
You may share a PBIX file with sample data or an excel sample file to test the same.
You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.
-------------------------------------------------
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
But the code you have sent is the same.
@HugoJesus
I replaced TempTable2 with TempTable.
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
I still receive the same error, "This expression refers to multiple columns..." using the the "TempTable".
Regards,
Hugo Jesus
Please share your PBIX with sample data removing any confidential data / information.
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hi @Fowmy
I've found how to share the PBIX, is now available to download, in the below link.
Password: 22092020
Regards
Hugo Jesus
PBIX
Example.PBIX
@HugoJesus
You have tried to add the code as a new column. You need to add it as a new table. Go to Modeling Tabl > New Table and paste it. I see the Cumulative Total coming up. I added it as a new Table
You can download the file: HERE
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Yes, I understand that and if you saw the PBIX, already exist a table with that code "Total_Open_Tickets".
But, my idea is to have an x-axis visualizations with that column, instead of a table.
Something like this, but with that column "Open_Tickets".
If look up to the example (image below), you saw two lines, the bottom line (Open_Tickets) that's the correct value but inserted manually, and the second line I'm using the code that you have sent but unsing in a Measure.
Regard's
Hugo Jesus
@HugoJesus
Sorry, I did not understand what sort of visualization you are after.
Below is your data, now can you explain how the expected result should come out?
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
My idea is only to show the column "Open_Tickets" in a x-axix visualization by Calendar.
Did you understand?
Thanks.
Regard's
Hugo Jesus
@HugoJesus , Your table structure is not clear.
I have a blog on a similar topic : https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
And cumulative of open(current employee added to file attached after signature .
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Hi,
This is a different level and my idea is to have the "Open_Tickets" in Area Chart by date.
The code that I'm using is the same that you have sent before, but a little different at the end.
Create a Measure:
Any idea how to solve this.
Regard's
Hugo Jesus
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 |
---|---|
117 | |
104 | |
77 | |
73 | |
50 |
User | Count |
---|---|
145 | |
109 | |
108 | |
90 | |
64 |