Iteration with multiple column conditions

[Khalefa]

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%

[tamerj1]

@Khalefa 
Please refer to attached sample file with the final solution.

Weighted Score = 
SUMX (
    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
        )
    )
)

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

% Score = 
DIVIDE (
    [Weighted Score],
    [Total Weight]
)

[Khalefa]

@tamerj1 thanks a lot, it works but if you don't mind, i need to add a small part which is:-

need to keep the condition of ( Compliance and Image ) active even if filtered by the slicer on the category.

by other means the condition must be applied even if i filtered by category3 ( which doesn't contain either compliance or image )

[tamerj1]

Hi @Khalefa 
Check if this is what your looking for. 

[Khalefa]

@tamerj1  it doesn't work as category 3 (Validation & Language) in review ID 2 should be Zero since there is a compliance of zero.
I updated the data on the drive as well

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

[tamerj1 · Accepted solution]

Hi @Khalefa 
Please refer to attached amended sample file

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

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

% Score = 
DIVIDE (
    [Weighted Score],
    [Total Weight]
)

[Khalefa]

@tamerj1  it worked fine, thanks a lot for the support

[Khalefa]

@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

[tamerj1]

@Khalefa 

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

[Khalefa]

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.

[v-yueyunzh-msft]

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

[Khalefa]

@v-yueyunzh-msft  thanks but i still need to keep the condition of ( Compliance and Image ) active even if filtered by the slicer on the category.

by other means, the condition must be applied even if I filtered by category3 ( which doesn't contain either compliance or image )
https://drive.google.com/drive/folders/1Zkd_6sS582BE8NfHxITV1Gku4njHsk4A?usp=sharing

[v-yueyunzh-msft]

Hi, @Khalefa 

Oh..According to your description, i get it that you just want to convert the calculated column to Measure , Right?

If this , you can try to use this dax as a measure:

Weighted score =

var _cur_ID = MAX('Fact'[Review ID])

var _t =FILTER( ALLSELECTED( 'Fact') , [Review ID] = _cur_ID)

var _t2 = ADDCOLUMNS( _t , "flag" , var _score =SELECTCOLUMNS( FILTER(_t , [Attribute] in {"Compliance","Image"}) , "Score",[Score]) return IF({0} in _score , 0 ,1))

var _t3 = ADDCOLUMNS( _t2 , "weighted_score" , var _flag = [flag] var _score = [Score] var _weight = RELATED('Dim_Attribute'[Weight]) return IF(_flag=0,0,IF(_score =0 , 0, _weight)))

return

SUMX(_t3, [weighted_score])

Then we can meet your need and we can keep the condition with the Category slicer , like this:

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

[Khalefa]

@v-yueyunzh-msft  thanks for the effort. it looks very structured but it still didn't give the required numbers as the total should = 145 and if I added the category below the review id in a table visual, it shouldn't give the total score of the review repeated in every category row

and it you could explain the steps to follow a long will be much appreciated, thanks again

[Khalefa]

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

[tamerj1]

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
    )
)

[Khalefa]

@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

[tamerj1]

@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 

[Khalefa]

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

[Khalefa]

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

[tamerj1]

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