Find value in matrix from other table and highlight

[makol76]

Hi guys

here is my issue :

I have a table which is a planning of audits : first column is the area, first line is the months of the year, and inside the matrix the name of the auditor.

I have another table which is the result of the audit, with the date, the area and the name of the auditor. 

What I want to achieve is :

Compare the results to the planning  : if the auditor made the audit at the right date in the right area, then highlight in green the cell and count 1 for the month. If not, red and 0

I am sure there is a clever measure to do this, but much too clever for me 🙂

if anyone could give a clue, I would appreciate

thanks

[makol76]

Hi @v-zhangti !

thanks for your prompt and clear answer. Yes, this is something very close to what you got. Compared to your solution, it would be great if :

- the name of the auditor can be shown, instead of 1 or 0

- the one or zero are a bad way to calculate a % of completion of the planning per month, which I would like to have also

your measure is already a great step for me, I need now to understand it and try to achieve the last 2 points. If any idea, I'll take it !

thanks again

Makol76

[makol76]

@v-zhangti  : thanks to your measure, I was able to create the matrix with the names and the colors, and it works !! thanks !

Now, my colleague want to display a curve of completion % : ratio between audit done in time vs audits planified. Easy for the audits planified. But for the audits done in time, a bit trickier. The measure before works in a matrix, but how to get the same thing without matrix ? As an old Excel user, I thought creating a column with a concatenate of date/area/auditor on both planified and realized, and compare the 2, but it's a bit to "excel" I think.

thanks 

[v-zhangti]

Hi, @makol76 

What kind of outcome do you expect? It can be expressed in Excel.

Best Regards,

Community Support Team _Charlotte

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

[makol76]

Hi @v-zhangti 

I found a way using a unique key column and a measure with filter and related.

thanks again for your help !!

regards

makol76

[v-zhangti · Accepted solution]

Hi, @makol76 

You can try the following methods.

Sample data:

Plan:

Result:

Measure:

Measure =
CALCULATE (
    COUNT ( Result[Result] ),
    FILTER (
        ALL ( Result ),
        [Area] = SELECTEDVALUE ( Plan[Area] )
            && [Date] = SELECTEDVALUE ( Plan[Date] )
            && [Auditor] = SELECTEDVALUE ( Plan[Auditor] )
    )
) + 0

Color = IF([Measure]=1,"Green",IF([Measure]=0,"Red"))

Result:

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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