The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hello
there are 3 columns in my Excel (Count , OpenedDate , ClosedDate)
I want to create a dash on PowerBi Desktop like this ,
How can I do it ?
Solved! Go to Solution.
Hi @zgrshn,
According to your description, I made a sample for your reference.
I assume you have a table called "MyTestTable" like below.
1. Add a new table called "StatusTable".
2. Use the formula below to create a calculate column called "Status" in "MyTestTable" table.
Status = IF(ISBLANK(MyTestTable[ClosedDate]), "Opened","Closed")
3. Use the formula below to create a measure called "Value".
Value = IF ( ISBLANK ( CALCULATE ( COUNTROWS ( MyTestTable ), FILTER ( MyTestTable, MyTestTable[Status] = FIRSTNONBLANK ( StatusTable[Status], 1 ) ) ) ), IF ( FIRSTNONBLANK ( StatusTable[Status], 1 ) = "Opened", SUM ( MyTestTable[Count] ), 0 ), SUM ( MyTestTable[Count] ) )
4. Use Matrix and Stacked column chart to show the data on the report.
Here is the sample pbix file for your reference.
Regards
Can you explain where the numbers are coming from in your result?
I write the numbers manually in Excel .
Hi @zgrshn,
According to your description, I made a sample for your reference.
I assume you have a table called "MyTestTable" like below.
1. Add a new table called "StatusTable".
2. Use the formula below to create a calculate column called "Status" in "MyTestTable" table.
Status = IF(ISBLANK(MyTestTable[ClosedDate]), "Opened","Closed")
3. Use the formula below to create a measure called "Value".
Value = IF ( ISBLANK ( CALCULATE ( COUNTROWS ( MyTestTable ), FILTER ( MyTestTable, MyTestTable[Status] = FIRSTNONBLANK ( StatusTable[Status], 1 ) ) ) ), IF ( FIRSTNONBLANK ( StatusTable[Status], 1 ) = "Opened", SUM ( MyTestTable[Count] ), 0 ), SUM ( MyTestTable[Count] ) )
4. Use Matrix and Stacked column chart to show the data on the report.
Here is the sample pbix file for your reference.
Regards
Really like what you've done, easy to understand.
Can you help a bit further with that formula (VALUE) if there are 4 status values? I don't have a count column, I just have a status column for each ticket with the following indicator: open, closed, pending and resolved.
(FYI-resolved and closed mean 2 different things)
Thank you in advance!
Dear JerryLi
Thank you very much
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
137 | |
106 | |
105 | |
73 | |
59 |
User | Count |
---|---|
266 | |
127 | |
119 | |
100 | |
88 |