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
Anonymous
Not applicable

Logic removes total undesirably

Greetings. Below I have an example of a DAX formula where the FILTER isn't behaving as I expect. I want it to only calculate the rows (hours) where today's conversion is lower than the 10th percentile of the same data. It just calculates it as if the FILTER statement isn't there for every row.

 

awoodhouse_0-1600721288396.png

 

However, if I use the VARs I created at the top as the basis for the filter, it works. I don't know enough about FILTER to understand why, but I'm guessing that using VARs is comparing the values in the table, whilst FILTER is iterating through a copy of the data in the data source and doing its own magic, producing undesired results.

 

awoodhouse_1-1600721569340.png

 

This is great, but I don't get my total now, because the total number isn't lower than the 10th percentile. I need that total, as I want to calculate an overall number as the basis for another DAX formula's total.

 

Any ideas on how to solve this? I truly have no idea.

2 ACCEPTED SOLUTIONS
nandic
Memorable Member
Memorable Member

Have you tried using "hasonevalue" function?
Using this function you can create separate calculation for total vs detail row.

Example:

IF (
    HASONEVALUE ( Table[Granularity] ),
    SUM ( Table[Calculation1] ),
    AVERAGE (Table[Calculation2] )
)

 

View solution in original post

lbendlin
Super User
Super User

There are two things at play here (actually three)

 

1. Variables help you preserve data across context transitions. This means they have no effect in your first example, but do have an effect in your second example where you use CALCULATE.

2. When you write measures you will want to write them from the perspective of the totals collection. Oftentimes that also works for the individual "rows".  The reverse is seldom true (writing a measure for the individual cell rarely works for the totals)

3. You reference other measures in your measure.  We have no idea what's happening in those measures.  General advice is to avoid nested measures both for performance and also to avoid surprises caused by the way those measures are designed and may be interfering with your calculations.

 

Please show the definitions for the other measures, provide usable sample data, and show the expected outcome.

View solution in original post

3 REPLIES 3
lbendlin
Super User
Super User

There are two things at play here (actually three)

 

1. Variables help you preserve data across context transitions. This means they have no effect in your first example, but do have an effect in your second example where you use CALCULATE.

2. When you write measures you will want to write them from the perspective of the totals collection. Oftentimes that also works for the individual "rows".  The reverse is seldom true (writing a measure for the individual cell rarely works for the totals)

3. You reference other measures in your measure.  We have no idea what's happening in those measures.  General advice is to avoid nested measures both for performance and also to avoid surprises caused by the way those measures are designed and may be interfering with your calculations.

 

Please show the definitions for the other measures, provide usable sample data, and show the expected outcome.

Anonymous
Not applicable

@lbendlin you second point made a lot of sense. I am going to change the logic of the filter and the overall calculation with this in mind and see if I can solve it. If I remain stuck I'll come back and post more details as requested.

nandic
Memorable Member
Memorable Member

Have you tried using "hasonevalue" function?
Using this function you can create separate calculation for total vs detail row.

Example:

IF (
    HASONEVALUE ( Table[Granularity] ),
    SUM ( Table[Calculation1] ),
    AVERAGE (Table[Calculation2] )
)

 

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.

Top Solution Authors