Forum Discussion
Helper IIhelp with Qlik to Dax
Hi Experts,
I would like to ask to convert this Qlik expression to DAX. Thanks
sum(aggr(count(distinct [Customer]),[Customer]))
Certainly! The Qlik expression you provided involves aggregation functions, and in DAX (Data Analysis Expressions), you can achieve similar results using the SUMX and DISTINCTCOUNT functions. Here's the equivalent DAX expression
SUMX(
VALUES('YourTableName'[Customer]),
CALCULATE(
DISTINCTCOUNT('YourTableName'[Customer])
)
)Replace 'YourTableName' with the actual name of your table in your Power BI or other DAX-compatible tool.
Explanation:
- VALUES('YourTableName'[Customer]): This function returns a table that contains the unique values of the [Customer] column.
- DISTINCTCOUNT('YourTableName'[Customer]): This function calculates the distinct count of customers within the current context.
- CALCULATE(): This function modifies the context in which the expression is evaluated, allowing you to use the DISTINCTCOUNT within the context of the specified table.
The SUMX function then iterates through each row of the table created by VALUES('YourTableName'[Customer]) and sums up the distinct count for each customer.
Make sure to replace 'YourTableName' with the actual name of your table and [Customer] with the actual name of your customer column.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.
1 Reply
- 123abc
Community Champion
Certainly! The Qlik expression you provided involves aggregation functions, and in DAX (Data Analysis Expressions), you can achieve similar results using the SUMX and DISTINCTCOUNT functions. Here's the equivalent DAX expression
SUMX(
VALUES('YourTableName'[Customer]),
CALCULATE(
DISTINCTCOUNT('YourTableName'[Customer])
)
)Replace 'YourTableName' with the actual name of your table in your Power BI or other DAX-compatible tool.
Explanation:
- VALUES('YourTableName'[Customer]): This function returns a table that contains the unique values of the [Customer] column.
- DISTINCTCOUNT('YourTableName'[Customer]): This function calculates the distinct count of customers within the current context.
- CALCULATE(): This function modifies the context in which the expression is evaluated, allowing you to use the DISTINCTCOUNT within the context of the specified table.
The SUMX function then iterates through each row of the table created by VALUES('YourTableName'[Customer]) and sums up the distinct count for each customer.
Make sure to replace 'YourTableName' with the actual name of your table and [Customer] with the actual name of your customer column.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.