topic Re: Iteration with multiple column conditions in DAX Commands and Tips

Iteration with multiple column conditions

Khalefa — Fri, 30 Dec 2022 16:34:28 GMT

Hi team

Need support in the below

a measure to calculate the overall attribute score on the review ID granularity considering the below:-
1- There are 6 attributes in each review id
2- The DAX should say that on each review id, if (attribute = compliance, and score =0 or attribute = image and score =0 ) so all the scores of all attributes within that review id must equal 0 regardless of scores of 100
3- If the above condition is False, so if the score =100, sum the weight otherwise give 0
4- The column weight is in another dimension table

Sample table

Review ID	Attribute	Score	Weight
1	Validation	100	10
1	Compliance	100	30
1	Image	100	30
1	Knowledge	0	10
1	Language	0	10
1	Accuracy	100	10
2	Validation	100	10
2	Compliance	0	30
2	Image	100	30
2	Knowledge	0	10
2	Language	100	10
2	Accuracy	0	10
3	Validation	0	10
3	Compliance	0	30
3	Image	0	30
3	Knowledge	100	10
3	Language	100	10
3	Accuracy	0	10
4	Validation	100	10
4	Compliance	100	30
4	Image	100	30
4	Knowledge	100	10
4	Language	100	10
4	Accuracy	100	10

Desired result

Review ID	Overall score	Weight	Score %
1	80	100	80%
2	0	100	0%
3	0	100	0%
4	100	100	100%
Total	180	400	45%

Re: Iteration with multiple column conditions

tamerj1 — Fri, 30 Dec 2022 17:01:36 GMT

Hi Khalefa
Please try

% Score = IF ( 0 IN CALCULATETABLE ( VALUES ( 'Table'[Score] ), 'Table'[Attribute] IN { "Compliance", "Image" } ), 0, SUMX ( 'Table', 'Table'[Score] * 'Table'[Weight] / 100 ) / 100 )

Re: Iteration with multiple column conditions

CNENFRNL — Fri, 30 Dec 2022 21:03:18 GMT

Re: Iteration with multiple column conditions

FreemanZ — Sat, 31 Dec 2022 04:24:07 GMT

hi Khalefa

How is the Sample table related to your Dimension Table?

Re: Iteration with multiple column conditions

Khalefa — Sun, 01 Jan 2023 23:36:32 GMT

the weight column is in a dimension table with a unique list of attributes

Re: Iteration with multiple column conditions

Khalefa — Sun, 01 Jan 2023 23:41:25 GMT

CNENFRNL thanks for the code yet i would need to understand why did add the overall weighted DAX to another iteration in overall DAX, rather than doing one iteration in one time.
another part, the weight column is in a dimension table with a unique list of attributes

Re: Iteration with multiple column conditions

Khalefa — Sun, 01 Jan 2023 23:43:12 GMT

tamerj1thanks a lot,
I tried the code but it gave me zero for everything

Re: Iteration with multiple column conditions

v-yueyunzh-msft — Mon, 02 Jan 2023 02:00:47 GMT

Hi , Khalefa

I see that the CNENFRNL have been realized in his dax. But for your "another part, the weight column is in a dimension table with a unique list of attributes". But what dimension table 's structure in your side?

So is there a relationship between your dimension table and your sample data?
Can you provide your original table structure? For example, provide your dimension table in the form of a table?

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Re: Iteration with multiple column conditions

tamerj1 — Mon, 02 Jan 2023 05:14:07 GMT

Hi Khalefa
Please refer to attached sample file with the solution (2 options)

% Score = AVERAGEX ( VALUES ( Scores[Review ID] ), IF ( 0 IN CALCULATETABLE ( VALUES ( Scores[Score] ), Scores[Attribute] IN { "Compliance", "Image" } ), 0, SUMX ( CALCULATETABLE ( Scores ), Scores[Score] * RELATED ( Weights[Weight] ) / 100 ) / 100 ) )

% Score2 = AVERAGEX ( VALUES ( Scores[Review ID] ), VAR T1 = CALCULATETABLE ( Scores ) RETURN IF ( ISEMPTY ( FILTER ( T1, Scores[Attribute] IN { "Compliance", "Image" } && Scores[Score] = 0 ) ), SUMX ( T1, Scores[Score] * RELATED ( Weights[Weight] ) / 100 ) / 100, 0 ) )

Re: Iteration with multiple column conditions

Khalefa — Mon, 02 Jan 2023 12:16:53 GMT

Just a simple question, How can I add the PBiX file

Re: Iteration with multiple column conditions

Khalefa — Mon, 02 Jan 2023 12:17:40 GMT

i just need to upload the model sample to be clear enough

Re: Iteration with multiple column conditions

tamerj1 — Mon, 02 Jan 2023 12:26:44 GMT

Hi Khalefa
You can upload to any file transfer service like dropbox, onedrive, googledrive etc.. and share the link.

Re: Iteration with multiple column conditions

Khalefa — Mon, 02 Jan 2023 12:58:47 GMT

I have added here the model for more visibility:-
1- weight column is in a dim table
2- I need to use all the dim to filter the fact
3- the dax should consider that if attributes of Compliance or image are zero so all attributes of that review ID are zero other wise sum score * weight at the review level granularity

https://drive.google.com/drive/folders/1Zkd_6sS582BE8NfHxITV1Gku4njHsk4A?usp=sharing

Re: Iteration with multiple column conditions

tamerj1 — Mon, 02 Jan 2023 13:23:22 GMT

Hi Khalefa
I hope this is what you're looking for. Please refer to attached sample file.

% Score = AVERAGEX ( VALUES ( 'Fact'[Review ID] ), IF ( 0 IN CALCULATETABLE ( VALUES ( 'Fact'[Score] ), Dim_Attribute[Attribute] IN { "Compliance", "Image" } ), 0, SUMX ( CALCULATETABLE ( 'Fact' ), 'Fact'[Score] * RELATED ( Dim_Attribute[Weight] ) / 100 ) / 100 ) )

Re: Iteration with multiple column conditions

Khalefa — Mon, 02 Jan 2023 14:36:07 GMT

tamerj1 can i ask why did you use calculatetable after sumx rather than just sumx
on the other side, you referred to attribute from dim table rahter than attribute from fact. will this matter ?

Thanks for your patiance

Re: Iteration with multiple column conditions

Khalefa — Mon, 02 Jan 2023 14:46:58 GMT

tamerj1
the results should be as follow

total weighted score (145 ) / total weight (400)

Sumx output should be divided by total weight not just 100

Review ID	Site	Channel	Attribute	Score	Weight	Weighted score
1	Site1	Call	Validation	100	15	15
1	Site1	Call	Compliance	100	0	0
1	Site1	Call	Image	100	0	0
1	Site1	Call	Knowledge	0	40	0
1	Site1	Call	Language	0	15	0
1	Site1	Call	Accuracy	100	30	30
2	Site1	Call	Validation	100	15	0
2	Site1	Call	Compliance	0	0	0
2	Site1	Call	Image	100	0	0
2	Site1	Call	Knowledge	0	40	0
2	Site1	Call	Language	100	15	0
2	Site1	Call	Accuracy	0	30	0
3	Site2	Chat	Validation	0	15	0
3	Site2	Chat	Compliance	0	0	0
3	Site2	Chat	Image	0	0	0
3	Site2	Chat	Knowledge	100	40	0
3	Site2	Chat	Language	100	15	0
3	Site2	Chat	Accuracy	0	30	0
4	Site2	Chat	Validation	100	15	15
4	Site2	Chat	Compliance	100	0	0
4	Site2	Chat	Image	100	0	0
4	Site2	Chat	Knowledge	100	40	40
4	Site2	Chat	Language	100	15	15
4	Site2	Chat	Accuracy	100	30	30

Re: Iteration with multiple column conditions

tamerj1 — Mon, 02 Jan 2023 14:49:51 GMT

Khalefa

CALCULATETABLE is required for context transition otherwise 'Fact' will be filtered only as per the original filter context filter context i.e. the filters on the visual, slicers, filter pane etc.. while we need the table filtered by the currently iterated Review ID.

Regarding your 2nd question, the answer is no, it makes no difference

Re: Iteration with multiple column conditions

tamerj1 — Mon, 02 Jan 2023 15:55:15 GMT

Khalefa

145/400 = 36% and this is exactly the result obtained at the total level. Am I missing something?

Re: Iteration with multiple column conditions

Khalefa — Mon, 02 Jan 2023 16:01:47 GMT

I just want to get the total score from your solution without percentage and another DAX for the total weight. this is what I meant.

Re: Iteration with multiple column conditions

v-yueyunzh-msft — Tue, 03 Jan 2023 03:12:18 GMT

Hi , Khalefa

Thanks for your quick response and your sample .pbix file to us!

Here are the steps you can refer to :
(1)We can create a calculated column in 'Fact' Table:

Weighted score = var _cur_ID = [Review ID] var _t =SELECTCOLUMNS( FILTER('Fact' , 'Fact'[Review ID] = _cur_ID && 'Fact'[Attribute] in {"Compliance","Image"}) , "Score",[Score]) var _weight = RELATED('Dim_Attribute'[Weight]) var _cur_sore = [Score] return IF( {0} in _t , 0 , IF(_cur_sore=0,0, _weight))

(2)Then we can create these measures:

Total Weight = SUMX( VALUES('Fact'), RELATED('Dim_Attribute'[Weight]))

Total Weight 2 = SUMX( ALL('Fact'), RELATED('Dim_Attribute'[Weight]))

Score % = SUM('Fact'[Weighted score]) / [Total Weight]

(3)Then we can put the fieds we need on the visual and we can meet your need:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly