Forum Discussion

karun_r's avatar
karun_r
Microsoft Employee
8 years ago

ISFILTERED Usage

I have a table structure like below:     Company       Category  FiscalYear  Revenue AdW               Bikes        FY17          200 AdW               Cars         FY17           99 AdW       ...
Need Help
Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
8 years ago

Hi karun_r

 

I think this calculated measure, when used on a visual gets close

 

Range = 
VAR myRev = CALCULATE(SUM('RevTable'[Revenue]), FILTER( ALLSELECTED('RevTable'),'RevTable'[Company] = MAX('RevTable'[Company])))
RETURN 
    SWITCH(
        TRUE() ,
        myRev >= 10 && myRev <= 50 , "A" ,
        myRev >  50 && myRev <= 160 , "B" ,
        myRev > 160 && myRev <= 300 , "C" ,
        myRev > 300, "D" ,
        "Other"
    )

I have a basic PBIX file here that I think helps show my understanding of the issue.

 

https://1drv.ms/u/s!AtDlC2rep7a-oxTPbNXgwkIdeMij

karun_r's avatar
karun_r
Microsoft Employee
8 years ago

Hi Phil_Seamark

 

Thanks for the reply.

 

I've gone through your example, and although it is an exact replica of what I've mentioned here, it is quite not what I am looking for.

 

For example, in a seperate table, I am showing the customer the total number of companies by the range they fall in for each fiscal year.  Now, if I do the same with the example pbi that you've sent, it has the exact issue that I am facing.

 

You can see that even though the number of categories are 2 (B & C) it is considering only B but not C. The reason for this is still a mystery to me. Same thing is happening in my report as well.

 

In an attempt to fix this problem, I tried including the Category into the ALLEXCEPT function and failed miserably and ended up with lot many dummy test measures.

 

  • karun_r's avatar
    karun_r
    Microsoft Employee
    8 years ago

    I think the reason for why only the range B is appearing in the table is because of the MAX function used in the Range definition, is my intuition correct ? Phil_Seamark

    • Phil_Seamark's avatar
      Phil_Seamark
      Microsoft Employee
      8 years ago

      HI karun_r

       

      What is the expected result you are after?  Are you trying to show a differnet number fo the [Count of Company] from the visual table on the right?

      • karun_r's avatar
        karun_r
        Microsoft Employee
        8 years ago

        Yes. Ideally, since we have selected few categories, the table on the right should show the number of companies that fall within each range value and it should take all the filters provided into context when it determines the range using the revenue.  I've been struggling with figuring out how to take the "categories" into context without messing up the range assignments.

         

        In the example pbi, with the filters as in the attached image, my ideal result would look like

         

        Range       #Companies

        B                 1

        C                 1