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?