Creation of a matrix based on existing values and categorizing non-existing values

[EmericTms]

Hello,

I would like your help regarding the creation of a specific matrix in power bi.

I created an simple excel to better explain my issue :

I have a table with 3 columns. And I would like to have a power bi matrix that takes the column "Entreprise" as lines, the column "Chapitre" as columns, and for the values : 1 is the status from the column "statut" is "Commencé", 2 if it is "En cours", 3 is it is "En retard".
But I also want to have 4 if for the "Entreprise" there is no "chapitre". For example, I would like my matrix to show 4 for "Entreprise" : "HP" and "Page" = "3" because it is not present in the table but it is still relevant for me.

This I can do.

What I can't manage to do is adding the fact that some precise combinations are Non Applicable. For example, "Apple" and chapitre "4" are not applicable and so is "Microsoft" and chapitre "3", and for these 2 I would like the matrix to have the value 5 and not 4. 

Do you know if it is possible ?

Thank you for your help !

Best regards,

ET

[v-denglli-msft · Accepted solution]

Thanks for the reply from rajendraongole1, please allow me to provide another insight.

Hi @EmericTms ,

Please refer to the following steps.

The data table is as follows.

Creates a disconnected table with a single column. Contains distinct values of the 'Chapitre' column.

DisconnectedTable = VALUES('Table'[Chapitre])

Create the following measure as the value of the matrix.

Measure = 
VAR current_enterprise = SELECTEDVALUE('Table'[Enterprise]) --The current cell's Enterprise.
VAR current_statut = CALCULATE(SELECTEDVALUE('Table'[Statut]), 'Table'[Chapitre] = SELECTEDVALUE('DisconnectedTable'[Chapitre])) --The current cell's Statut.
RETURN
IF(current_statut <> BLANK(), 
    SWITCH(current_statut,
        "Commencé", 1,
        "En cours", 2,
        "En retard", 3
    ),
    SWITCH(current_enterprise, --If current_statut is blank it means that this combination is not applicable.
    "HP",4,
    "Page", 4,
    "Microsoft",5,
     "Apple", 5
    )
)

Use Enterprise as the rows of the matrix, the field of the disconnected table as the columns of the matrix, and the measure as the matrix value.

Hopefully it will meet your needs.

Please see the attached pbix for reference.

Best Regards,
Dengliang Li

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

[EmericTms]

Hello,

Thank you for the response. After a few modifications it seems to be working ! But on the same idea, there is a matrix that I can't get to work with the formula you sent, could you also help me with this one too? (sorry I'm struggling with Power BI). 

I have these two tables (same format as before but different names), they are related through the id of the first table.

I created the first matrix as you instructed me and it works : 

but I can't manage to make this one works :

Do you have an idea how to make it work?

Here is the power bi with the tables, the matrix and the formula :

test pbi 

Thank you.

ET

[v-denglli-msft]

Hi @EmericTms ,

Please try the following measure.

Measure 2 = 
VAR current_chapter = MAX('Tableau2'[Chapter])
VAR current_company = CALCULATE(SELECTEDVALUE(Tableau1[Company]),REMOVEFILTERS(Tableau2))
VAR current_score = MAX(Tableau1[Score])
RETURN
IF(current_score <> BLANK(), 
    SWITCH(current_score,
        1, 1,
        2, 2,
        3,3
    ),
    SWITCH(SELECTEDVALUE('DisconnectedTable'[Chapter]),
        "4b",SWITCH(current_company, "Apple", 4, 5), 
        "1a",SWITCH(current_company, "Tesla", 4, 5),
        5
    )
)

Please see the attached pbix for reference.

Best Regards,
Dengliang Li

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

[EmericTms]

Thank you, I accepted the previous answer as the solution !

[rajendraongole1]

Hi @EmericTms  - Yes! You can achieve this in Power BI by creating a calculated measure that assigns the correct values based on the different conditions you specified. 

create a measure as below:

Status_Value =
VAR _Status = SELECTEDVALUE('Table'[Statut])
VAR _Entreprise = SELECTEDVALUE('Table'[Entreprise])
VAR _Chapitre = SELECTEDVALUE('Table'[Chapitre])

-- Define Non Applicable Cases
VAR _NonApplicable =
OR(
(_Entreprise = "Apple" && _Chapitre = 4),
(_Entreprise = "Microsoft" && _Chapitre = 3)
)

RETURN
SWITCH(
TRUE(),
_Status = "Commencé", 1,
_Status = "En cours", 2,
_Status = "En retard", 3,
_NonApplicable, 5, -- Assign 5 for Non Applicable cases
4 -- Default to 4 for missing Chapitre-Entreprise pairs
)

check it and let know.

[EmericTms]

Hello, thank you for the answer. I tried it, but it is not working, everything return as 4 and I have no 5.