Forum Discussion

DavidH's avatar
DavidH
Icon for Helper I rankHelper I
7 years ago
Solved

How to extract results based on selection

Greetings!   As see form the numerous posts there is a mixture of 'neewbies' and experienced datanauts here so I thought I would post something that I am struggling to get to grips with!   I have...
Need Help
Tips and Tricks
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    hi, DavidH 

    I suggest you try this data model:

    1. Create a Employee Name fact table then create a relationship with Employees table

    2. Use this formula to create a measure

    Measure = var Scoreneeded=CALCULATE(MAXX('Employee Name',CALCULATE(SUM(Employee[Level]))),ALLSELECTED('Employee Name')) return
IF(Scoreneeded=CALCULATE(MAXX('Employee Name',CALCULATE(SUM(Employee[Level])))), "Authorised" , "Not Authorised")

    Result:

    here is sample pbix file, please try it.

     

    Best Regards,
    Lin

v-lili6-msft's avatar
v-lili6-msft
Icon for Community Support rankCommunity Support
7 years ago

hi, DavidH 

You could try this way:

In Edit Queries, Select Code1, Code2, Code3, Code4 then Unpivot them, 

Now you could create the relationship with Employees table and use Service for slicer.

 

Best Regards,

Lin

 

DavidH's avatar
DavidH
Icon for Helper I rankHelper I
7 years ago

Lin,

 

Many thanks for your comments!

 

Having followed your suggestion I have the information arranged now with a new table (Reference) with the unpivoted coluimns. I have created the relationship between the tables now as follows;

 

Service - Service (Unpivot as Reference) 1:Many

Service (Unpivot as Reference) - Employees Many:Many

 

Just wondering if this is the correct way to approach this? However, with the report view I have also created a measure to determine the 'Score needed' based on the slicer selection which works fine.

 

I can display the Employees on a Matrix visual which shows what they results are....but what I would like to now do is physically show on the matrix who is 'Authorised' and who is 'Not Authorised'.

 

Is there any way I can use the measure or include something in the Matrix to show this?

 

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    hi, DavidH 

    I suggest you try this data model:

    1. Create a Employee Name fact table then create a relationship with Employees table

    2. Use this formula to create a measure

    Measure = var Scoreneeded=CALCULATE(MAXX('Employee Name',CALCULATE(SUM(Employee[Level]))),ALLSELECTED('Employee Name')) return
IF(Scoreneeded=CALCULATE(MAXX('Employee Name',CALCULATE(SUM(Employee[Level])))), "Authorised" , "Not Authorised")

    Result:

    here is sample pbix file, please try it.

     

    Best Regards,
    Lin