Highlight in scatter plot using field parameter

[DK-C-87]

I got this DAX formula to highlight dots in a scatter plot using 1 og 0 in conditional formatting. 
It works great, except when I use the field parameter slicer to choose multiple filters

Highlight Valgte Muligheder =

VAR Opslag = ALLSELECTED('Highlight indhold'[Valgmuligheder])

VAR Business_Cases = IF(MAX(HighligtGBS[HL - Business Cases]) IN Opslag, 1, 0)

VAR Storbageri = IF(MAX(HighligtGBS[HL - Storbageri]) IN Opslag, 1, 0)

VAR Bageri = IF(MAX(HighligtGBS[HL - Bageri]) IN Opslag, 1, 0)

VAR Resultat = Business_Cases + Storbageri + Bageri

VAR MultiResultat = IF(Resultat > 1, CALCULATE(DISTINCTCOUNT(HighligtGBS[Plant]), 'Highlight indhold'[Valgmuligheder] IN Opslag), 0)

RETURN

IF(HASONEFILTER('Highlight indhold'[Valgmuligheder]), Resultat, IF(ISCROSSFILTERED('Highlight indhold'[Valgmuligheder]), MultiResultat, BLANK()))

I also have a counter who shows how many dots that has been highlighted in the scatter plot, and that DAX works everytime. It shows the correct number all the time

Highlighted butikker = COUNTX(HighligtGBS, IF([Highlight Valgte Muligheder] > 0, 1))-0

Can anyone please help me correct my DAX, so it works as good as my counter DAX formula and dosent highlight any dot when the counter is 0

[Kedar_Pande · Accepted solution]

@DK-C-87 

Measure:

Highlight Valgte Muligheder =
VAR Opslag = VALUES('Highlight indhold'[Valgmuligheder]) -- Captures all currently selected values
VAR Business_Cases = IF(CONTAINS(Opslag, 'Highlight indhold'[Valgmuligheder], MAX(HighligtGBS[HL - Business Cases])), 1, 0)
VAR Storbageri = IF(CONTAINS(Opslag, 'Highlight indhold'[Valgmuligheder], MAX(HighligtGBS[HL - Storbageri])), 1, 0)
VAR Bageri = IF(CONTAINS(Opslag, 'Highlight indhold'[Valgmuligheder], MAX(HighligtGBS[HL - Bageri])), 1, 0)
VAR Resultat = Business_Cases + Storbageri + Bageri
VAR MultiResultat = IF(Resultat > 0, 1, 0) -- Assign 1 if at least one condition is met

RETURN
IF(
HASONEFILTER('Highlight indhold'[Valgmuligheder]),
Resultat,
IF(ISCROSSFILTERED('Highlight indhold'[Valgmuligheder]), MultiResultat, BLANK())
)

Let me know if this resolves the issue!

If this helped, a Kudos 👍 or Solution mark would be great!

Cheers,

Kedar Pande

www.linkedin.com/in/kedar-pande

[DK-C-87]

Is there anyone who can please solve this issue ?

If I could take the counter in as a parameter in my main DAX it would solve the problem, but I am getting a circular reference fail - probely because of the countX formula in the counter DAX formula and maybe the fact that I use my main formula "Highlight Valgte Muligheder" in the statement: 

Highlighted butikker =
COUNTX(HighligtGBS, IF([Highlight Valgte Muligheder] > 0, 1))-0

 
But If I simple could change this part in my main formula:

...
RETURN
IF(HASONEFILTER('Highlight indhold'[Valgmuligheder]), Resultat, IF(ISCROSSFILTERED('Highlight indhold'[Valgmuligheder]), MultiResultat, BLANK()))

to this: 

...
RETURN
IF(HASONEFILTER('Highlight indhold'[Valgmuligheder]), Resultat, IF(ISCROSSFILTERED('Highlight indhold'[Valgmuligheder] && [Highlighted butikker] > 0), MultiResultat, BLANK()))

then it would solve the problem.
The counter is always right. 

The fail in my main formula is that it highlights (gives 1 to) the ID's that only have one of the two selected in the slicer. So if I select "Bageri" and "Business Cases" there are 4 ID's that have one of the two, and therefore it highligths it in the graph, but I could only be the ID's how have both "Bageri" and "Business Cases" - as the counter formula tells. 

Hopes this helps someone to see a solution for me, please 

[v-yilong-msft]

Hi @DK-C-87 ,

Yes, I did try to reproduce what you stated.

But I realized that I am not able to know exactly what form your table data is in.

Or if you can provide me with the data from the 'Highlight indhold' form and the 'HighligtGBS' form.

Please provide relevant example data and specific .pbix files if you can.

Best Regards

Yilong Zhou

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

[DK-C-87]

Hi Yilong Zhou
I have made a example .pbix file os the data, but somehow I can't see where to upload it in my reply to you. - do you know how to upload a file in here ?
When I was making the example file for you it came to me that I have a slicer that filters on three types of departments. And the issue with the formula and the dots that needs to be or not be hightlighted occurs when there is no selections in the department slicer. 
So somehow I think I have to add this to the formula, so if there is no selected department in the slicer then all should be blank () 
- If you get what I mean by this, maybe you could help with that ? 

[v-yilong-msft]

Hi @DK-C-87 ,

For some new community members, they don't have the permission to upload .pbix files. You may need to share file links via DropBox, One Drive, Drive or any other tool.

Best Regards

Yilong Zhou

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

[Kedar_Pande · Accepted solution]

@DK-C-87 

Measure:

Highlight Valgte Muligheder =
VAR Opslag = VALUES('Highlight indhold'[Valgmuligheder]) -- Captures all currently selected values
VAR Business_Cases = IF(CONTAINS(Opslag, 'Highlight indhold'[Valgmuligheder], MAX(HighligtGBS[HL - Business Cases])), 1, 0)
VAR Storbageri = IF(CONTAINS(Opslag, 'Highlight indhold'[Valgmuligheder], MAX(HighligtGBS[HL - Storbageri])), 1, 0)
VAR Bageri = IF(CONTAINS(Opslag, 'Highlight indhold'[Valgmuligheder], MAX(HighligtGBS[HL - Bageri])), 1, 0)
VAR Resultat = Business_Cases + Storbageri + Bageri
VAR MultiResultat = IF(Resultat > 0, 1, 0) -- Assign 1 if at least one condition is met

RETURN
IF(
HASONEFILTER('Highlight indhold'[Valgmuligheder]),
Resultat,
IF(ISCROSSFILTERED('Highlight indhold'[Valgmuligheder]), MultiResultat, BLANK())
)

Let me know if this resolves the issue!

If this helped, a Kudos 👍 or Solution mark would be great!

Cheers,

Kedar Pande

www.linkedin.com/in/kedar-pande

[DK-C-87 · Accepted solution]

Hi Again Kedar_Pande

I thing i found the issue. It is because I have a colomn with three different department for each ID in the same tabel where I check for my Field Parameter such as "Business Cases" and "Storbageri" etc.
The department is in a slicer, and when I select one department everything in your DAX formula works great !
Could you please tell have to add to your DAX if all departments are selected in the slicer it return 0.
So if there is non selected in the department slicer the measure should only give 1 to the ID's that have multiselected (F.eks. Business Cases and Storbageri) in the same department under each ID ?

I really hope you can help me, because you are so close to the answer 🙂

[DK-C-87]

When I select "Business Cases" and "Bageri" it highlights 25 dots in stead of 4 - and the counter in the buttom left corner also counts 25 dots. But the real answer is 0 dots. 
If I change the Variable: 

VAR MultiResultat = IF(Resultat > 0, 1, 0) -- Assign 1 if at least one condition is met

To

VAR MultiResultat = IF(Resultat > 1, 1, 0) -- Assign 1 if at least one condition is met

It highlights 4 dots in the scatter plot and count 0 (the correct answer) in the buttom left corner. In other words, it sends me right back to where I started. 

I hope this maybe helps you to see a new solution ?

I need it to highlight 0 dots, same as the counter, because der is non of the ID's that have both "Bageri" and "Business Cases" - hope this clarifies it 

I have been struggling with this for 2 weeks know 🙂