topic Re: DAX not working in DAX Commands and Tips

DAX not working

Anees91 — Tue, 10 Dec 2024 11:53:57 GMT

Hi guys I'm working on a Pbi dashboard, mainly it's working fine, however I have an issue. so i have two columns for example, count of failure code and total delivery volume (including failure) .. i divide the failure code by total delivery volume for %. I have a bar chart showing % with failure code such as a, b, c,d on x axis and then volume of failure on y axis, shown as bar, line is shown for % failure (combo chart). When I click on a bar for specific code, i can see the correct failure % on the line as well as on a separate gauge visual. However when i use a date slicer, i only see the correct % on the the bar chart but not gauge visual when i click on failure code on bar chart.

This is the formula i'm using:

Service Success =

VAR Numerator = CALCULATE(COUNT('Volume'[Failure.ConsignmentId]))

VAR Denomiator = CALCULATE(COUNT(Volume[ConsignmentId]),

REMOVEFILTERS(Volume[Failure.SFCCode]),

KEEPFILTERS(ALLSELECTED(Volume[Failure.SFCCode]))

)

RETURN

IF(

Denomiator = 0,

BLANK(),

1- (Numerator / Denomiator)

)

I've edited the interaction so that the combo chart does not interact with total delivery volume which is shown on a separate card visual as this card should be showing all deliveries - successful and failed

Appreciate any help please! Thanks

Re: DAX not working

DataNinja777 — Tue, 10 Dec 2024 15:03:54 GMT

Anees91 ,

The issue you’re encountering arises from the filter context when interacting with multiple visuals, particularly the bar chart and the gauge visual, combined with the date slicer. The discrepancy occurs because your Service Success measure uses ALLSELECTED and REMOVEFILTERS, which can sometimes reset or alter the filter context unexpectedly.

To address this, you can refine your measure to ensure consistent behavior across all visuals. Modify the measure as follows:

Service Success = VAR Numerator = CALCULATE( COUNT('Volume'[Failure.ConsignmentId]), REMOVEFILTERS('Volume'[Failure.SFCCode]) ) VAR Denominator = CALCULATE( COUNT('Volume'[ConsignmentId]), REMOVEFILTERS('Volume'[Failure.SFCCode]) ) RETURN IF( Denominator = 0, BLANK(), 1 - (Numerator / Denominator) )

This updated formula ensures that the SFCCode filter is removed consistently, allowing the measure to correctly calculate percentages even when interacting with slicers or visuals. Additionally, verify that the date slicer’s filtering context is applied correctly to the visuals by reviewing the relationships between your date column and the Volume table.

Make sure the interactions between the visuals are properly configured in Power BI. Check that the bar chart filters the gauge visual as intended while the date slicer applies to both the bar chart and the gauge visual. You can do this by going into "Edit Interactions" and adjusting the behavior to suit your needs.

If the problem persists, you can experiment with removing REMOVEFILTERS in the measure to see if it resolves the issue. Testing the gauge visual with and without slicer interaction can help pinpoint where the filter context is being lost. Additionally, using the Performance Analyzer in Power BI can provide insight into how the filter context is being applied during interactions. This will help ensure that all visuals are synchronized and displaying the correct calculations.

Best regards,

Re: DAX not working

Anees91 — Tue, 10 Dec 2024 15:38:42 GMT

Thank you for replying, I have tried them to no success, I've even tried this where I would specify on a different column instead :

Service Success v1 =

VAR Numerator = CALCULATE(COUNT('Volume'[Service Failure.ConsignmentId]),

'Volume'[Failed] = "Fail")

VAR Denomiator = CALCULATE(COUNT(Volume[ConsignmentId])

)

RETURN

IF(

Denomiator = 0,

BLANK(),

1- (Numerator / Denomiator)

)

I was hoping this would have solved the issue as then the x axis on the bar chart is referring to a different failure column with different Categories. The number 1 in the measure means it's all fail. This measure seems fine, however again when i click on a bar filter - Failure.SFCCode, the measure this time goes to 0% as its not reflecting the filter i clicked on in the bar chart - I have checked the interactions and it seems fine

Re: DAX not working

Sahir_Maharaj — Tue, 10 Dec 2024 16:42:47 GMT

Hello Anees91,

Can you please try this approach:

Service Success = 1 - DIVIDE( COUNT('Volume'[Failure.ConsignmentId]), CALCULATE( COUNT('Volume'[ConsignmentId]), REMOVEFILTERS('Volume'[Failure.SFCCode]), ALLSELECTED('Volume') ) )

Re: DAX not working

Anees91 — Tue, 10 Dec 2024 16:59:40 GMT

Hi Sahir, thanks for replying. Unfortunately this still gives me the same issue .. I have no page filters set if that helps to clarify anything further

Re: DAX not working

Poojara_D12 — Wed, 11 Dec 2024 05:40:47 GMT

Hi Anees91

Try adjusting the formula for Service Success to use ALL instead of ALLSELECTED for the gauge visual calculation, so that the slicer context does not affect it, while still respecting the selection made in the bar chart:

Service Success = VAR Numerator = CALCULATE(COUNT('Volume'[Failure.ConsignmentId])) VAR Denominator = CALCULATE( COUNT(Volume[ConsignmentId]), REMOVEFILTERS(Volume[Failure.SFCCode]), KEEPFILTERS(ALLSELECTED(Volume[Failure.SFCCode])) // Keeps filter for failure codes ) VAR DenominatorGauge = CALCULATE( COUNT(Volume[ConsignmentId]), REMOVEFILTERS(Volume[Failure.SFCCode]), ALL(Volume[Date]) // Removes the date slicer filter context for gauge visual ) RETURN IF( Denominator = 0, BLANK(), 1 - (Numerator / Denominator) )

Please consider:

If you are using multiple slicers or complex filtering, ensure that the right context is applied for both your chart and gauge visual.
You may also need to check the interactions between the visuals to ensure the slicer and filters are behaving as expected (especially for the gauge visual).

This approach should resolve the issue with the gauge visual not updating correctly when clicking on failure codes in the bar chart while still respecting the slicer for the combo chart.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS

Re: DAX not working

Anonymous — Wed, 11 Dec 2024 06:23:08 GMT

Hi Anees91 ,

Consider using summarize to create a Code's dimension table and then filter it. Try to share some sample data along with expected results.

Best regards,
Community Support Team_ Scott Chang

Re: DAX not working

Anees91 — Wed, 11 Dec 2024 09:05:01 GMT

Hi, for example, if I have 16 failure volume out of 100 total volume, the % should be 16/100 * 100 = 16%, then i would display this result as 100 - 16 = 84%. This will be shown on the gauge visual. I also have combo chart where the x axis would be categories for failure volume, for example, a,b,c,d all showing 4 failure each for each category. If I click on the a specific category, the line on the chart will be the % showing 4/100 = 4% then 100-4% = 96%. This will be shown on the line on the combo chart however the gauge visual does not always reflect the same values (sometimes it does for a random category but not always) .. same measure used for the graph and gauge visual ..

Re: DAX not working

Anonymous — Wed, 11 Dec 2024 09:31:43 GMT

Hi Anees91 ,

I made simple samples and you can check the results below:

Table 2 = SUMMARIZE('Table',[Category]) Measure = var _s = SELECTEDVALUE('Table 2'[Category]) var _sum = COUNT('Table'[ID]) var _total = CALCULATE(COUNT('Table'[ID]),REMOVEFILTERS('Table'[Category]),FILTER('Table',[Fail]="Yes")) var _selecet = CALCULATE(COUNT('Table'[ID]),FILTER('Table',[Fail]="Yes"&&[Category]=_s)) RETURN IF(HASONEFILTER('Table 2'[Category]),1-DIVIDE(_selecet,_sum),1-DIVIDE(_total,_sum))

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.