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

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
CVR1392
Regular Visitor

QlikView Script to PowerBI

Hi PBI Geeks,

 

I am moving from Qlik to PBI. Can you help me with the DAX query for the Qlik View script below?

 

1.  If(Match(Upper([Field]), 'Value1', 'Value2'), (Resolved - Opened), (Today() - Opened)) AS Ticket_Age, 

2.  If(Match(Upper([Field]),  'Value1', 'Value2'), null(), (Today() - Opened)) AS Ticket_Open Age,

 

Note: Resolved and Opened are Date Fields 

 

Thank you in Advance.

CVR

 

1 ACCEPTED SOLUTION
Martin_D
Super User
Super User

With MyTable being the table that contains columns Field (text), Resolved (date), and Opened (date):

 

 

let
    AddColumnTicketAge = 
        Table.AddColumn(
            MyTable, 
            "Ticket_Age", 
            each 
                Duration.Days(
                    if List.PositionOf({"", Text.Upper("Value1"), Text.Upper("Value2")}, Text.Upper([Field]), 1) > 0
                    then ([Resolved] - [Opened]) 
                    else (Date.From(DateTime.LocalNow()) - [Opened])
                ),
            Int64.Type),
    AddColumnTicketOpenAge = 
        Table.AddColumn(
            AddColumnTicketAge, 
            "Ticket_Open Age", 
            each 
                Duration.Days(
                    if List.PositionOf({"", Text.Upper("Value1"), Text.Upper("Value2")}, Text.Upper([Field]), 1) > 0
                    then null 
                    else (Date.From(DateTime.LocalNow()) - [Opened])), 
                    Int64.Type
                )
in
    AddColumnTicketOpenAge

 

 

 The Power Query language is called M (for Meshup), not DAX.

View solution in original post

4 REPLIES 4
CVR1392
Regular Visitor

Hi Martin,

 

It is working fine now. I have replaced "MyTable" (line 4)  from your scriptwith Tickets (my table name) instead of my query's previous step table name. 

 

Thank you,

CVR

Martin_D
Super User
Super User

With MyTable being the table that contains columns Field (text), Resolved (date), and Opened (date):

 

 

let
    AddColumnTicketAge = 
        Table.AddColumn(
            MyTable, 
            "Ticket_Age", 
            each 
                Duration.Days(
                    if List.PositionOf({"", Text.Upper("Value1"), Text.Upper("Value2")}, Text.Upper([Field]), 1) > 0
                    then ([Resolved] - [Opened]) 
                    else (Date.From(DateTime.LocalNow()) - [Opened])
                ),
            Int64.Type),
    AddColumnTicketOpenAge = 
        Table.AddColumn(
            AddColumnTicketAge, 
            "Ticket_Open Age", 
            each 
                Duration.Days(
                    if List.PositionOf({"", Text.Upper("Value1"), Text.Upper("Value2")}, Text.Upper([Field]), 1) > 0
                    then null 
                    else (Date.From(DateTime.LocalNow()) - [Opened])), 
                    Int64.Type
                )
in
    AddColumnTicketOpenAge

 

 

 The Power Query language is called M (for Meshup), not DAX.

Thank you for the quick response. I have repalced from the script "MyTable" with Tickets (my table name) and "Value1" and "Value2" with Resolved and Closed ( Both are values in my field TicketState ) and "Field" with TicketState (my field)  but I am getting the following error. Can you please help me with the error?

CVR1392_0-1687795633754.png

 

Looks Like this question requires the other queries as well for Investigation and a screenshot of the query dependencies (at the View ribbon, button Query Dependencies). Or provide a sample file.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.