Forum Discussion

CVR1392's avatar
CVR1392
Regular Visitor
3 years ago
Solved

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

 

Need Help
  • Martin_D's avatar
    Martin_D
    3 years ago

    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.

4 Replies

  • CVR1392's avatar
    CVR1392
    Regular Visitor
    3 years ago

    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's avatar
    Martin_D
    Solution Sage
    3 years ago

    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.

    • CVR1392's avatar
      CVR1392
      Regular Visitor
      3 years ago

      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?

       

      • Martin_D's avatar
        Martin_D
        Solution Sage
        3 years ago

        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.