How to use Inscope to get all data for all hierarchy of matrix

[Anonymous]

Hi Team

Below is my matrix

to get food waste ratio - I am using below dax

<p>Food waste ratio kg/meals =</p>
<p>var _rt = DIVIDE(</p>
<p>    [Sum Food waste (kg)],</p>
<p>    [Number of Meals],</p>
<p>    0)</p>
<p>var regionselected = ISINSCOPE(Sitewise_Food_Waste[Region])</p>
<p>var countryselected = ISINSCOPE(Sitewise_Food_Waste[Country Name as per IFM])</p>
<p>var siteseleted =ISINSCOPE('Sitewise_Food_Waste'[Description])</p>
<p>return</p>
<p>IF(regionselected,</p>
<p>SWITCH(SELECTEDVALUE(Sitewise_Food_Waste[Region]),</p>
<p>    "Americas",0.125683264508758,</p>
<p>    "APAC",0.0557190941291727,</p>
<p>    "NORDICS",0.05659,</p>
<p>    "ECA",0.0596213566615247),</p>
<p>IF(AND(countryselected,NOT(regionselected)),</p>
<p>SWITCH(SELECTEDVALUE(Sitewise_Food_Waste[Country Name as per IFM]),</p>
<p>"Thailand",0.17310184,</p>
<p>"Vietnam",0.082248491,</p>
<p>"India",0.02667024,</p>
<p>"Singapore",0.086450345,</p>
<p>"Spain",0.053553922,</p>
<p>"Germany",0.052602516,</p>
<p>"Denmark",0.002933784,</p>
<p>"Sweden",0.06379112,</p>
<p>"Netherlands",0.027747326,</p>
<p>"Portugal",0.137438849,</p>
<p>"Monte Mor",0.154235745,</p>
<p>0.098882427),</p>
<p>IF(AND(siteseleted,NOT(countryselected)),</p>
<p>SWITCH(SELECTEDVALUE(Sitewise_Food_Waste[Description]),</p>
<p>"Hochheim",0.052602516,</p>
<p>"Limburg",0.052602516,</p>
<p>"Arganda del Rey",0.053553922,</p>
<p>"Heerenveen",0.027747326,</p>
<p>"Lisbon",0.137438849,</p>
<p>"Lund Råbyholm",0.06379112,</p>
<p>"MONTE MOR",0.154235745,</p>
<p>"PONTA GROSSA",0.098882427,</p>
<p>"BRAZIL OFFICES",0.125683265,</p>
<p>"Chakan Factory",0.02667024,</p>
<p>"(THAILAND) RAYONG PLANT",0.17310184,</p>
<p>"Binh Duong - New",0.082248491,</p>
<p>"SOUTH EAST ASIA",0.086450345,</p>
<p>0))))</p>
<p> </p>
<br /><br />

In this dax, I am trying to get different baseline rates for all sites, country and Region - all will be different with their total - but I am unable to get this - please help me. Please please please.... 😥

[amitchandak · Accepted solution]

@Anonymous , With your code is not very clear. what you want. As lot of HTML is part of that.

The Order of isinscope will be like this

Switch(True() ,

isinscope(Table[Description]), [Site level measure],

isinscope(Table[Country]), [Country level measure],

isinscope(Table[Region]), [Region level measure]

)

Can you share sample data and sample output in table format?

refer if needed

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/