Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
karun_r
Employee
Employee

ISFILTERED Usage

I have a table structure like below:

 

 

Company       Category  FiscalYear  Revenue

AdW               Bikes        FY17          200

AdW               Cars         FY17           99

AdW               Acces.      FY17           15

Acme              Bikes       FY17            299

Acme              Cars         FY17           150

Acme              Acces.     FY17           22

 

 

Now I am trying to assign a custom range label to each company based on the revenue after few filters are applied.

 

If the custom ranges are

 

A :  10-50

B: 50-160

C: 160-300

😧 300 - Above

 

If only Bikes are selected, Acme and Adw would fall under category C. If Bikes and Cars are selected, Acme would fall under D and Adw under C.

 

To achieve this behaviour, I created a measure which will aggregate the sum of the revenue of the company like below:

 

SumRevenue = CALCULATE(SUM(Revenue), ALLEXCEPT(RevTable, Company))

 

This works as expected and ignores the category division and returns the total sum revenue of the company.

 

I am then creating a custom column to assign label to each company based on their revenue.

 

But when I filter the category using a filter, even then, I still see the whole revenue of the company instead of just the category revenue. This should be expected because of the ALLEXCEPT in the measure.

 

To get the total revenue for one particular category through the filter, I added Category into ALLEXCEPT parameters and now the measure looks like this.

 

SumRevenue = CALCULATE(SUM(Revenue), ALLEXCEPT(RevTable, Company, Category))

 

 

The problem, the segment labeling(A,B,C,D) is happening for each category instead of for the entire company. This again is as expected because of the inclusion of the Category column in ALLEXCEPT.

 

Now my question is, how do I achieve both? When no filter is applied on the Category, the custom column with which the labels are being assigned, should conisder the entire revenue of the company instead of the category revenues. And when a filter is applied, it should take into account the revenue of that particular category and assign/change the label of the company.

 

Ideally, this should be the result when I use the second formula but it is still assigning labels based on the category revenues when when there is no filter on the Category.

 

 

To counter this, I tried using ISFILTERED function on Category column and based on it's outcome, I'm changing which version of the measure to use. Even this is not working and it is considering only the category revenues all the time. How do I go about fixing this one? I am sure I am missing some tiny details and any guidance would be helpful.

10 REPLIES 10
Phil_Seamark
Employee
Employee

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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. Capture.PNG

 

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.

 

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

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?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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

@Phil_Seamark, To give more context on what I'm doing, I've edited the sample pbi file and shared it here.

 

https://1drv.ms/u/s!AmE9ILAWJzWtgf9I75oB116mVNJi-Q

 

I added data for one more fiscal year and ideally what I am looking for is a comparison of number of customers in each range between fiscal years.

 

So my initial step was to get the range definition working for atleast 1 fiscal year then I can worry about comparing ranges from both fiscal years.

Hi @karun_r

 

I think I have what you are after.  It involves a disconnected table with a new measure called [# Companies]

 

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Hi Phil,

 

I've tried the approach suggested in your pbi file earlier with my original dataset and it did not work with it. The reason is the existence of multiple fiscal years and it complicates things a bit. For your reference, I've edited the sample pbi file and placed it here.

 

 

 

https://1drv.ms/u/s!AmE9ILAWJzWtgf9JbHAH0Oh_z3MIOw

 

I've added another fiscal year into the mix and created a seperate measure to calculate the number of companies for each range in the different table that you've created. You can see how it displays that few companies exist within range A even though all of them fall under B when the appropriate filters are selected.

 

DAX1.PNG

Hi @karun_r

 

So how do you want the Fiscal Year to affect your expected result?  Do you want to ignore it and still generate the same values?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Ideally, I want one value for each fiscal year. So, yes, the fiscal year must be considered when it assigns the range to the company. The end goal is to have a YoY comparison for each revenue range.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.