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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Chiniminiz
Frequent Visitor

Filter with a variable table in a Calculate function

Hello,

 

we want to create an automatic ticket analyse system for internal use. The target is to receive for the top 5 categories of tickets the most used words in the short description. 

We have two tables:

A list of Tickets with the category/service

TicketService
5645623Office
5645624Word
5645625Excel
5645626Excel
5645627Excel
5645628Excel
5645629Excel
5645630Word
5645631Excel
5645632Office
5645633Word
5645634Excel
5645635Office
5645636Office
5645637Office
5645638Office
5645639Office
5645640Office
5645641Office
5645642Office

 

and a list of words per ticket from the tickets shortdescription

TicketsText
5645623Office
5645623not
5645623working
5645624Title
5645624not
5645624underlined
5645625C1
5645625wrong
5645625title
5645626B1
5645626wrong
5645626title
5645627B1
5645627wrong
5645627Title
5645628B1
5645629C1
5645630Title
5645631B1
5645632Web Service
5645633Title
5645634B1
5645635Office
5645636Office
5645637Web Service
5645638Web Service
5645639Web Service
5645640Web Service
5645641Web Service
5645642Web Service

 

We have different customers and need the data per month. This we choose with a drop down visual.

 

To receive the top 5 categories/services we already made the code, which is working correctly. But when we insert a table with two columns: 1. Words from the short description, 2. the counted words from the short descrition

we receive the wrong counts of words.

 

 

Our code until here is:

 

Count Handling User Short Description #3 Service = 
VAR _Nth = 3

VAR _ReferenceService = ALLSELECTED(SV_INQUIRY[IT_SERVICE])

VAR _ServiceAndTickets = 
    ADDCOLUMNS(
        VALUES( SV_INQUIRY[IT_SERVICE] ),
        "Rank", RANKX( _ReferenceService, [Anzahl Tickets Handling User ohne PW])
    )

VAR _NthService = 
    MINX(_FilterNthProduct, SV_INQUIRY[IT_SERVICE])

RETURN
CALCULATE(
    COUNT(Handling_User[Wert]),
    FILTER(
        SV_INQUIRY,
        SV_INQUIRY[IT_SERVICE] = _NthService
    )
)

 

 

When we return the _NthService it is correct. So the Calculate function can't relate between the Handling_User table and the VAR _ServiceAndTickets with the [Rank] I think. 

 

The dashboard looks like this:

image.png

 

 

 

 

If you need further information, please tell me! 🙂

 

Please help and thanks in advance.

 

Best regards 

 

Patrick

3 REPLIES 3
v-chenwuz-msft
Community Support
Community Support

Hi @Chiniminiz ,

 

What does the formula for [Anzahl] look like?

 

Best Regards

Community Support Team _ chenwu zhu

Hi @v-chenwuz-msft,

 

this is a measure for distinct count the tickets. The real table has many more tickets, which shouldn't be counted. 

This is like 

DISTINCTCOUNT(Tickets[Tickets])

 

The question behind all is: Can we connect the threetables 

1. Tickets

2. Ticket-Shortdescription

3. VAR 

VAR _ServiceAndTickets = 
    ADDCOLUMNS(
        VALUES( SV_INQUIRY[IT_SERVICE] ),
        "Rank", RANKX( _ReferenceService, [Anzahl Tickets Handling User ohne PW])
    )

 

In my mind we should need a variable ranking system of the categories like 3. VAR _ServiceAndTickets which is from 1. Tickets. This we should connect to 2. Ticket-Shortdescription, because we want to get the words, per category of the top 5 categories, but in different measures, that we can get different word lists from them.

 

Do you think, this is even possible?

 

Thanks a lot für your expertise.

 

Best regards 

 

Patrick - Chiniminiz 

Hi @Chiniminiz ,

 

You can try using KEEPFILTERS() to add multiple current filter contexts.

 

Or please share your pbix file without sensitive information and with the output you want.

 

Best Regards

Community Support Team _ chenwu zhu

 

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors