Please correct this measure used in matrix to remove showing data at drill up level

[Hassa0213]

Hi,

I created 3 measures for ID, Date and Innovation Stage columns in my data table(Company_table) and the matrix visual is as shown below.

The columns used for rows in matrix visual as below.

* The first requirement is to show the highest Innovation stage(Innov_m) and highest date(Date_m) when drill up to company level and its working as shown below.

* Second requirement is I dont want to display the Innovation Stage(Innov_m) and date(Date_m) when drill up to Region Level but its displaying at region level as well as shown below.

The requirement is to show the highest Innovation stage and highest date only when its drill up to company level.

The 2 measures I used for the above matrix is as below, please help to correct the logic to get the desired output or let me know any other ideas. Thanks

Innov_m =

var stage_s = CALCULATE(MAX(Company_table[Inovation Stage]), ALLEXCEPT(Company_table,Company_table[Subject], Company_table[Company Name]))

return CALCULATE(MAX(Company_table[Inovation Stage]),FILTER(Company_table, (Company_table[Inovation Stage])= stage_s))

 ----------------------------------------------------------------------------

Date_m =

var date_s = CALCULATE(MAX(Company_table[Date]), ALLEXCEPT(Company_table,Company_table[Subject],Company_table[Company Name]))

return FORMAT(CALCULATE(MAX(Company_table[Date]),FILTER(Company_table, Company_table[Date] = date_s)), "dd-mm-yyyy")

[Hassa0213]

Hi All, Please let me know how to create a measure for ID column (ID_m).

* Requirement is not to show the ID when drill up to the Company and region level in the matrix. Thanks!

[Ashish_Mathur]

Hi,

Assuming ID_m is already a measure, write this measure

Measure1 = if(hasonevalue(Company_table[innovation stage]),[ID_m],blank())

Hope this helps.

[Bifinity_75 · Accepted solution]

Hi @Hassa0213 , Have you tried with the INSCOPE function?. Look at this little example:

The measure:

Max Date = if(ISINSCOPE(Table_[Company Name]),max(Table_[Date]),"-")

I hope it can work for you, greetings

[Hassa0213]

@Bifinity_75  Thanks alot. This worked.