Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
eagletro
Frequent Visitor

Retrive calls by System

Hi All,

 

retreving calls from our company ticket system and have found issues with validation (Now seen to it thats its fixed). Thus the vast majority of calls are system "select from list". i need a way of finding as close to the amount of tickets per system as possible. All i can think of is looking at the description field and search for the list of system's. Any idea of how to do this?

 

see screenshot below

 

Calls by system.PNG

1 ACCEPTED SOLUTION

Hi @eagletro,

however in the decription in the vast majority of cases will contain the system name. I want a way to search the description field to look for the system name's.

If I understand you correctly, you should be able to use FIND function(DAX) to search the description field to look for the system name's in your scenario. The formula(untested) below is for your reference. Smiley Happy

Count of Current System =
VAR currentSytemName =
    FIRSTNONBLANK ( Table1[SYSTEM NAME], 1 )
RETURN
    COUNTA ( 'Table1'[SYSTEM NAME] )
        + CALCULATE (
            COUNTA ( 'Table1'[SYSTEM NAME] ),
            FILTER (
                ALL ( Table1 ),
                'Table1'[SYSTEM NAME] = "Select from list"
                    && FIND ( currentSytemName, 'Table1'[DESCRIPTION],, -1 ) > 0
            )
        )

 

Regards

View solution in original post

6 REPLIES 6
vanessafvg
Super User
Super User

@eagletro not quite clear on what you asking are you wanting to know how many tickets have that issue?





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




The issue is that there shouldnt be any tickets that are system name "select from list" due to our systems poor validation it allows users to create ticket without a system name.

 

however in the decription in the vast majority of cases will contain the system name. I want a way to search the description field to look for the system name's.

 

system.PNG

 

 

Hi @eagletro,

however in the decription in the vast majority of cases will contain the system name. I want a way to search the description field to look for the system name's.

If I understand you correctly, you should be able to use FIND function(DAX) to search the description field to look for the system name's in your scenario. The formula(untested) below is for your reference. Smiley Happy

Count of Current System =
VAR currentSytemName =
    FIRSTNONBLANK ( Table1[SYSTEM NAME], 1 )
RETURN
    COUNTA ( 'Table1'[SYSTEM NAME] )
        + CALCULATE (
            COUNTA ( 'Table1'[SYSTEM NAME] ),
            FILTER (
                ALL ( Table1 ),
                'Table1'[SYSTEM NAME] = "Select from list"
                    && FIND ( currentSytemName, 'Table1'[DESCRIPTION],, -1 ) > 0
            )
        )

 

Regards

@eagletro so you only want data where the system name is populated but not with the ""select from list"  - if you using import mode in power query you can filter that data out.  is this what you asking?

 

when you say search, are you wanting to use a filter in the visual, can you just explain a little more as to the output you are expecting.





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




I want to see the amount of calls per system. Ive used the description as a way to do this. ive filtered select from list and on each system name to get a figure. not perfect but best i can do from the data.

 

select from list.PNG

@eagletro ok so are you saying you have extracted select * from the description? and then you are filtering on it?

 

i mean unless you have some other indicator to identify each system this makes sense to do, if you can build some kind of rule around it in the background and use that rule to generate a smaller column to filter on is that what you look for?   these sort of things are best done in power query rather than dax in my opinion





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.