Only count when all the selected values are present per row

[DK-C-87]

I hope there are someone out there that can help me with the last bit of DAX 🙂

HighlightGBS table in PowerBI (example): 

The "Helper" calculated column count non blanks per row:

Helper = 
VAR _1 = CALCULATE(DISTINCTCOUNT(HighlightGBS[Division]), HighlightGBS[HL - Business Cases] <> BLANK())
VAR _2 = CALCULATE(DISTINCTCOUNT(HighlightGBS[Division]), HighlightGBS[HL - Storbageri] <> BLANK())
VAR _3 = CALCULATE(DISTINCTCOUNT(HighlightGBS[Division]), HighlightGBS[HL - Bageri] <> BLANK())
VAR _4 = CALCULATE(DISTINCTCOUNT(HighlightGBS[Division]), HighlightGBS[HL - Egen Rengøring] <> BLANK())
VAR Samlet = _1 + _2 + _3 + _4
RETURN
Samlet

The columns "HL - Business Cases", "HL - Storbageri", "HL - Bageri" & "HL - Egen Rengøring" is info about the division and are used in field parameter slicer along with a scatterplot chart and a slicer for the subdivision: 

If I choose one value from the HIGHLIGHT slicer everything works great, but is a choose two or more from HIGHTLIGHT (and still SUBDIVISION is set to Alle/All then the counter is off. It seems to me that it counts every division that have either of the selected in HIGHTLIGHT. 
(The funny thing is, if I choose two or more in HIGHLIGHT slicer and uses the SUBDIVISION slicer to filter, everything works fine again)

The DAX I am using to hightlight is :

Highlight Valgte Muligheder = 
VAR Valgte = IF(ISCROSSFILTERED('Highlight indhold'[Valgmuligheder]), COUNTA('Highlight indhold'[Valgmuligheder]), 0)
VAR Opslag = VALUES('Highlight indhold'[Valgmuligheder]) -- this is the field parameter slicer HIGHLIGHT
VAR Business_Cases = IF(CONTAINS(Opslag, 'Highlight indhold'[Valgmuligheder], MAX(HighlightGBS[HL - Business Cases])), 1, 0)
VAR Storbageri = IF(CONTAINS(Opslag, 'Highlight indhold'[Valgmuligheder], MAX(HighlightGBS[HL - Storbageri])), 1, 0)
VAR Bageri = IF(CONTAINS(Opslag, 'Highlight indhold'[Valgmuligheder], MAX(HighlightGBS[HL - Bageri])), 1, 0)
VAR Egen_Ren = IF(CONTAINS(Opslag, 'Highlight indhold'[Valgmuligheder], MAX(HighlightGBS[HL - Egen Rengøring])), 1, 0)
VAR Resultat = Business_Cases + Storbageri + Bageri + Egen_Ren
VAR MultiResultat = IF(Resultat > 1 && CALCULATE(DISTINCTCOUNT(HighlightGBS[DIVISION]), HighlightGBS[Helper] > 1), 1, 0)

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

 It seems to me that when the subdivision is not filtred and I select multiple in the highlight slicer my DAX measure can't just can't ignore the subdivision and only give me the result from Division. 
In my DAX it is the VAR  MultiResultat that has got me stuck. Maybe the calculated helper column should be changed...

Please let me know, what I can do to make this work ? 🙂

[MFelix]

Hi @DK-C-87,

Believe that you question is about the fact that you are selecting the MAX values on your contains, this will only pick up one value for the comparision so when you select more than one you get incorrect values.

Be aware that without any data is difficult to pin point the error try the following expression:

Highlight Valgte Muligheder =
VAR Valgte =
    IF (
        ISCROSSFILTERED ( 'Highlight indhold'[Valgmuligheder] ),
        COUNTA ( 'Highlight indhold'[Valgmuligheder] ),
        0
    )
VAR Opslag =
    VALUES ( 'Highlight indhold'[Valgmuligheder] ) -- this is the field parameter slicer HIGHLIGHT
VAR Business_Cases =
    IF ( VALUES ( HighlightGBS[HL - Business Cases] ) IN Opslag, 1, 0 )
VAR Storbageri =
    IF ( VALUES ( HighlightGBS[HL - Storbageri] ) IN Opslag, 1, 0 )
VAR Bageri =
    IF ( VALUES ( HighlightGBS[HL - Bageri] ) IN Opslag, 1, 0 )
VAR Egen_Ren =
    IF ( VALUES ( HighlightGBS[HL - Egen Rengøring] ) IN Opslag, 1, 0 )
VAR Resultat = Business_Cases + Storbageri + Bageri + Egen_Ren
VAR MultiResultat =
    IF (
        Resultat > 1
            && CALCULATE ( DISTINCTCOUNT ( HighlightGBS[DIVISION] ), HighlightGBS[Helper] > 1 ),
        1,
        0
    )
RETURN
    IF (
        HASONEFILTER ( 'Highlight indhold'[Valgmuligheder] ),
        Resultat,
        IF (
            ISCROSSFILTERED ( 'Highlight indhold'[Valgmuligheder] ),
            MultiResultat,
            BLANK ()
        )
    

[DK-C-87]

Hi Miguel

Thank you for your answer. 
However det scatter plot visualization now comes with an error telling that there were delievered more values, where there only were expected a single.

Do you have a suggestion to fix this error ?