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
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
108 | |
105 | |
78 | |
35 | |
35 |
User | Count |
---|---|
157 | |
103 | |
71 | |
65 | |
53 |