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, thanks for your time. I have this scenario where iam using table to display the details data.. I would like to show the count of all incident numbers and this should change with the filters selected in slicers..
I have the dataset like
Ticket Number Date Created Resolved By Dept State Unique_Ticket_Number Total
101 07/01/2021 XYZ1 ABC Open 1 5
101 07/01/2021 XYZ1 XYZ Open 1 5
101 07/01/2021 XYZ1 HR Open 1 5
101 07/01/2021 XYZ1 FIN Open 1 5
101 07/01/2021 XYZ1 MAR Open 1 5
===
5
when i use the ticket number and use the count in table - it is not showing 5 in each row instead it is showing 5 at the end as total
but i want the 5 to be in each row - this should again change when we select the slicers..slicers as filters
any suggestions...thanks for your time
Solved! Go to Solution.
Hi @Raj007 ,
You can use the following measure:
Unique_Ticket_Number = CALCULATE(DISTINCTCOUNT('Table'[Dept]),FILTER(ALLSELECTED('Table'),'Table'[Ticket Number ] = MAX('Table'[Ticket Number ])))
Please refer to the pbix file.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Hi @Raj007 ,
You can use the following measure:
Unique_Ticket_Number = CALCULATE(DISTINCTCOUNT('Table'[Dept]),FILTER(ALLSELECTED('Table'),'Table'[Ticket Number ] = MAX('Table'[Ticket Number ])))
Please refer to the pbix file.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
[Total] =
CALCULATE(
COUNTROWS( TicketTable ),
ALLSELECTED( )
)
However, whether this works or not depends on the structure of your model which you have not shown.
Hi Daxer, thank you so much for your time. Let me give you some brief so you get an idea
1.I have an SQL query against SQL server - I have only 1 table in power BI - everything in one table
2. I have the dataset something similar to this - there are ticket number with multiple rows because of some values in other columns; there may be some ticket numbers where there is only 1 row
1). I want to build the table visual because i want to give all the fields as detail report
icket Number Date Created Resolved By Dept State Unique_Ticket_Number Total
101 07/01/2021 XYZ1 ABC Open 1 5
101 07/01/2021 XYZ1 XYZ Open 1 5
101 07/01/2021 XYZ1 HR Open 1 5
101 07/01/2021 XYZ1 FIN Open 1 5
101 07/01/2021 XYZ1 MAR Open 1 5
===
5
102 07/01/2021 XYZ1 ABC Open 1 3
102 07/01/2021 XYZ2 XYZ12 Open 1 3 102 07/01/2021 XYZ2 XYZ12 Open 1 3
102 07/01/2021 XYZ2 HR12 Open 1 3
===
3
using the slicers i want to give an option to select ticket number, dept, state etc -- primary fields as slicers
is it clear? the last column will give me the total number of rows for that ticket number
again, thanks a lot for your time
Regarding one-table models... You should be aware of the problems that await you: Auto-exist on clusters or numbers – Unplugged #22 - SQLBI
You should never let one-table models make it into production unless you like producing wrong numbers without even being aware of their existence.
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 |
---|---|
106 | |
105 | |
79 | |
68 | |
61 |
User | Count |
---|---|
143 | |
104 | |
103 | |
82 | |
70 |