Qliksense Possible and Exclude function in Power BI

[poojashribanger]

Hi All,

I am migrating application from qliksense to power Bi where i have 1 complex expresssion which i want to do in power BI.

count( distinct {$ <Fact_Flag={'App Object '}, _objectType={'invoice'},

 Status={'ready','rejected'},[%_invoice_id] = E({$<Status={'exported','closed'}, port= >}[%_invoice_id]), port= >}

    aggr({$< Fact_Flag={'App Object '}, _objectType={'invoice'}, 

Status={'ready','rejected'},[%_invoice_id] = E({$<Status={'exported','closed'}, port= >}[%_invoice_id]), port= >}

        FirstSortedValue( distinct

            {$<

                Fact_Flag={'App Object '}

                ,_objectType={'invoice'}

                ,Status={'ready','rejected'}

                ,port=

                ,[%_invoice_id] = E({$<Status={'exported','closed'}, port= >}[%_invoice_id])

>}

                                                                                  [%_invoice_id], _modifiedTimestamp, 1

        )

,Year ,MonthYear, [%_invoice_id] 

    )

)

Can someone please me with this.

Thanks & Regards,

Poojashri

[123abc · Accepted solution]

In Power BI, you can achieve similar complex calculations and aggregations as in QlikSense using DAX (Data Analysis Expressions). However, translating complex expressions from one platform to another can be challenging, and it may require breaking down the logic into smaller steps to ensure accuracy.

The expression you provided in QlikSense appears to involve aggregation, distinct count, filtering, and nested conditions. I'll attempt to break down the expression step by step in Power BI DAX, but keep in mind that the exact translation may require some adjustment based on your specific data model and requirements.

Here's a breakdown of the expression in Power BI DAX:

Complex Measure =
VAR FilteredData =
FILTER(
'YourTable',
'YourTable'[Fact_Flag] = "App Object" &&
'YourTable'[_objectType] = "invoice" &&
'YourTable'[Status] IN {"ready", "rejected"} &&
'YourTable'[%_invoice_id] IN
CALCULATETABLE(
'YourTable',
FILTER(
'YourTable',
'YourTable'[Status] IN {"exported", "closed"}
)
)
)

VAR GroupedData =
SUMMARIZE(
FilteredData,
'YourTable'[Year],
'YourTable'[MonthYear],
'YourTable'[%_invoice_id],
"MaxModifiedTimestamp",
CALCULATE(
MAX('YourTable'[_modifiedTimestamp]),
FILTER(
FilteredData,
'YourTable'[%_invoice_id] = EARLIER('YourTable'[%_invoice_id])
)
)
)

VAR FirstInvoiceID =
SUMMARIZE(
GroupedData,
'YourTable'[Year],
'YourTable'[MonthYear],
'YourTable'[%_invoice_id],
"MaxModifiedTimestamp",
CALCULATE(
MAX('YourTable'[_modifiedTimestamp]),
FILTER(
FilteredData,
'YourTable'[%_invoice_id] = EARLIER('YourTable'[%_invoice_id])
)
)
)

VAR Result =
COUNTROWS(FirstInvoiceID)

RETURN
Result

Please note the following:

• Replace 'YourTable' with the actual name of your table in Power BI.
• Ensure that the column names ('Fact_Flag', '_objectType', 'Status', etc.) match your data model.
• I've broken down the expression into multiple variables (FilteredData, GroupedData, FirstInvoiceID, and Result) to make it more readable and maintainable.

This Power BI DAX expression attempts to replicate the logic you provided in QlikSense, but it may require further refinement based on your specific data structure and requirements. Testing and validating the results with your data will be essential.

[poojashribanger]

Thanks for your valuable input, I tried this but i am getting below error it seems like something to do with IN operator.