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
csaethre19
Helper II
Helper II

Calculated Columns Affected By Slicer Filters

I have two calculated columns:

CalculatedColumn_FailureCount =
CALCULATE(
    COUNTROWS('public failure'),
    ALLEXCEPT('public failure', 'public failure'[Failure Mode], 'public failure'[Experiment Start Time])
) (this column is giving me the count based on the unique combinations of Failure Mode and Experiment Start Time)
 
and
 
CalculatedColumn_TotalRuns =
CALCULATE(
    DISTINCTCOUNT('public failure'[experimentId]),
    ALLEXCEPT('public failure', 'public failure'[Experiment Start Time])
) (this column is giving me the count of experimentIds to get a total run count for each day)
 
I have some slicers that are not affecting the counts provided in these calculated columns. They are correctly giving me the counts for the specified fields in these dax expressions but when I select a filter they are not re-calculated to provide an accurate count based on the filtered table. The table gets filtered but the values do not change based on the new filtered table and I need it to reflect that change. 
 
Is there a way I can do this?
csaethre19_0-1685983370224.png

 

Thanks!
1 ACCEPTED SOLUTION

@csaethre19 

I understood you wanted the overall for a day, Just do the below changes to the measures.

TotalRuns_Test =
CALCULATE (
DISTINCTCOUNT ( 'Test'[Experiment Id] ),
ALLEXCEPT ( Test, Test[Experiment Start Time] )
)

FailureRate_Test = [FailureCount_Test] / [TotalRuns_Test]

NaveenGandhi_0-1686063464053.png

 

Regards,
Naveen




View solution in original post

9 REPLIES 9
NaveenGandhi
Super User
Super User

Hello @csaethre19 

Calculated columns cannot alter its calculation based on a slicer from report view. Create these same calculations as measure. That will work fine.

Let me know if this helps!

If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!


Okay. Can you maybe point me in the right direction on how to accomplish a failure rate measure?

So far I have these measures: 

Measure_FailureCount = COUNT('public failure'[Failure Mode])
 
and 
 
Measure_TotalRuns =
CALCULATE(
    DISTINCTCOUNT('public failure'[experimentId]),
    REMOVEFILTERS('public failureMode'[Failure Type], 'public failureMode'[Designation])
)
 
I need a third measure to calculate the failure rate based on these two measures.
I have this:
Measure_FailureRate = [Measure_FailureCount] / [Measure_TotalRuns]
 
However, when I graph this failure rate vs. Experiment Start Time on a line chart and set the Legend to Failure Mode I get incorrect values. It appears to be dispalying just the Failure Count multiplied by 100. It does not consider the divide by total runs per day. 
Here are some screenshots of some data I am looking at:
csaethre19_1-1685986830575.png

 

csaethre19_2-1685986838625.png

 

csaethre19_3-1685986852698.png

As you can see from the table on May 1st I should show 20% for the failure mode in blue.


 

 

@csaethre19 

Can you provide sample data to understand the problem better, please make sure you include all the columns that will be required for calculation and visualisation.

Regards,

Naveen

https://drive.google.com/file/d/10tOYSEt6w-_WfEBi6L-oaamE1il9c6ws/view?usp=sharing 

I have shared the .pbix file that is a simple idea of what I am trying to do. When setting the legend on the failure rate graph to failure mode you get incorect rates for each mode on each day. Something similar is happening in my real data set when I have these same measures. 

@csaethre19 

Check the attached PBIX.  I have made a test table( Just to alter the data points little bit to show variation in the chart) and a test page with the line chart. Use the Failure_Rate_Test measure that i have created as it will give the desired output.

Let me know if this helps and if any questions!


If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!

Thank you for taking the time to provide this sample. However, it still does not acheive the desired results. If you look at the failure counts in the table you see that the overall failure rate is what the failure rate column is telling you but when you look at the graph you can see that the overall rate is given to two different failure modes. What I would expect is that the two failure modes for that day would show their individual failure rate. For instance, on May 04 there was a total of 4 failure counts - 1 for mode1 and 3 for mode3. The total runs for that day was 6 so for mode1 the failure rate should be 1/6 and for mode2 it should be 3/6. These two failure rates make up the 66.67% that is shown for both of these modes on the graph. 

csaethre19_0-1686062646629.png

csaethre19_1-1686062691703.png

 

csaethre19_2-1686062738184.png

 

@csaethre19 

I understood you wanted the overall for a day, Just do the below changes to the measures.

TotalRuns_Test =
CALCULATE (
DISTINCTCOUNT ( 'Test'[Experiment Id] ),
ALLEXCEPT ( Test, Test[Experiment Start Time] )
)

FailureRate_Test = [FailureCount_Test] / [TotalRuns_Test]

NaveenGandhi_0-1686063464053.png

 

Regards,
Naveen




thank you thank you thank you!!!

@csaethre19 

can you provide access for the file.

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.