Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I'm not able to optimize this measure. I'm new to Power BI DAX. Kindly suggest me a way to run this DAX faster.
If I am wrong, please suggest me any other alternative way to achieve the below DAX
DAX Measure:
Measure name =
SUMX (
SUMMARIZE (table_name',
table_name'[Col1],table_name'[Col2],table_name'[col3],table_name'[col4],table_name'[col5],
"result",
CALCULATE (
DISTINCTCOUNTNOBLANK ( table_name'[col1] ),
FILTER (
table_name',
SUM ( table_name'[Counter] ) = 1
)
)
),
[result]
)
@tamerj1 @johnt75 @Greg_Deckler -
I have created a temporary column using concat and counter column in the dataset.
temp_column = table_name[col1], table_name[col2]. table_name[col3], table_name[col4], table_name[col5]
Counter = 1 if the couter sum = 1 else 0
And I updated this DAX.
CALCULATE(
DISTINCTCOUNTNOBLANK ( TableName [Temp_column] ),
KEEPFILTERS ( tableName [Counter] = 1 )
)
Still, I'm facing the issue. The value is not matching. DAX is really difficult, please help me on this.
Please try
MeasureName =
SUMX (
SUMMARIZE (
FILTER ( table_name, table_name[Counter] = 1 ),
table_name[Col1],
table_name[Col2],
table_name[col3],
table_name[col4],
table_name[col5],
"result", COUNTROWS ( VALUES ( table_name[col1] ) )
),
[result]
)
Please try
MeasureName =
SUMX (
FILTER (
SUMMARIZE (
table_name,
table_name[Col1],
table_name[Col2],
table_name[col3],
table_name[col4],
table_name[col5],
"@Result", COUNTROWS ( VALUES ( table_name[col1] ) ),
"@Counter", SUM ( table_name[Counter] )
),
[@Conter] = 1
),
[@Result]
)
Hi @tamerj1 - I'm getting the value now. It loads much faster. But still we have more granularity, hence it is not loading in the matrix. It takes much time
@rishari
Try
MeasureName =
SUMX (
SUMMARIZE (
table_name,
table_name[Col1],
table_name[Col2],
table_name[col3],
table_name[col4],
table_name[col5],
"@Result", COUNTROWS ( VALUES ( table_name[col1] ) ),
"@Counter", SUM ( table_name[Counter] )
),
IF ( [@Conter] = 1, [@Result] )
)
Its not best practice to use SUMMARIZE to add calculated columns, its better to use ADDCOLUMNS, e.g.
MyMeasure =
SUMX (
ADDCOLUMNS (
SUMMARIZE (
'table_name',
'table_name'[Col1],
'table_name'[Col2],
'table_name'[col3],
'table_name'[col4],
'table_name'[col5]
),
"result",
CALCULATE (
DISTINCTCOUNTNOBLANK ( 'table_name'[col1] ),
FILTER ( 'table_name', SUM ( 'table_name'[Counter] ) = 1 )
)
),
[result]
)
This will return a single value, its just an optimized version of your code.
@rishari Can you post sample data so can test and optimize.
User | Count |
---|---|
60 | |
22 | |
18 | |
18 | |
16 |
User | Count |
---|---|
86 | |
54 | |
54 | |
38 | |
21 |