Calculation depending on specific value

[Anonymous]

Hi,

I have problems to create a conditional measure. I have the following measure, which calculates the fillrate of my data depending on several fields. My data has these columns:

This is my measure which works fine, but is without condition.

Technologie = 
VAR neededFieldsList = {"Genutzte Technologien", "Hosting", "Datenbank-Server", "Datenbank", "Datenbank-Typ", "Datenbank-Version", "Applikationsserver", "Betriebssystem"}
VAR blankFields = 
    COUNTAX(
        FILTER(
            AP,
            [Feld] IN neededFieldsList
        ),
        AP[Wert]
    )
RETURN
    DIVIDE( 
        blankFields,
        CALCULATE(
            DISTINCTCOUNT([Feld]),
            FILTER(
                AP,
                [Feld] IN neededFieldsList
            )
        )
    )

Now I have the condition that if the value of the field "Hosting" is "SaaS", I do not want to take all these fields into account but only two of them. I do not know how I can achieve this in my measure. My approach was this:

Technologie = 
VAR neededFieldsListSaaS = {"Genutzte Technologien", "Hosting"}
VAR neededFieldsList = {"Genutzte Technologien", "Hosting", "Datenbank-Server", "Datenbank", "Datenbank-Typ", "Datenbank-Version", "Applikationsserver", "Betriebssystem"}

VAR blankFieldsSaaS = 
    COUNTAX(
        FILTER(
            AP,
            [Feld] IN neededFieldsListSaaS
        ),
        AP[Wert]
    )
VAR blankFields = 
    COUNTAX(
        FILTER(
            AP,
            [Feld] IN neededFieldsList
        ),
        AP[Wert]
    )
RETURN
    IF(
        SELECTEDVALUE(AP[Wert])="SaaS",
        DIVIDE( 
            blankFieldsSaaS,
            CALCULATE(
                DISTINCTCOUNT([Feld]),
                    FILTER(
                        AP,
                        [Feld] IN neededFieldsListSaaS
                    )
            )
        ),
        DIVIDE( 
            blankFields,
            CALCULATE(
                DISTINCTCOUNT([Feld]),
                    FILTER(
                        AP,
                        [Feld] IN neededFieldsList
                    )
            )
        )
    )

But it does not work according to my data. How can I do something like this?

[Anonymous]

Does anyone have another idea how to solve this? I am stuck on this.

[amitchandak]

@Anonymous , refer if one of the two can help

calculate( IF(
SELECTEDVALUE(AP[Wert])="SaaS",
DIVIDE(
blankFieldsSaaS,
CALCULATE(
DISTINCTCOUNT([Feld]),
FILTER(
AP,
[Feld] IN neededFieldsListSaaS
)
)
),
DIVIDE(
blankFields,
CALCULATE(
DISTINCTCOUNT([Feld]),
FILTER(
AP,
[Feld] IN neededFieldsList
)
)
)
), values(AP[Wert]))

or

averageX(values(AP[Wert]), IF(
SELECTEDVALUE(AP[Wert])="SaaS",
DIVIDE(
blankFieldsSaaS,
CALCULATE(
DISTINCTCOUNT([Feld]),
FILTER(
AP,
[Feld] IN neededFieldsListSaaS
)
)
),
DIVIDE(
blankFields,
CALCULATE(
DISTINCTCOUNT([Feld]),
FILTER(
AP,
[Feld] IN neededFieldsList
)
)
)
))

[Anonymous]

Please excuse my late answer. I tried to answer, but the page showed me some html error. Both approaches do not seem to work. I have two test cases where I can see that the calculation is not depending on the value "Hosting" in the column "Feld".

Just for clarification, my data looks like this. I renamed the column headers. Here are two examples of two different apps. Example 1:

For this application I expect the result of my measure to be 100,00%, because the value in the column "Feld" is "Hosting" and the value in the column "Wert" is "SaaS". Therefore only the rows with the values "Hosting" and "Genutzte Technologien" in the column "Feld" shall be used for the calculation. As these are both not empty the result shall be 100,00%. 

Example 2:

In this example I expect the result to be 50,00%  because the value in the column "Feld" is "Hosting" and the value in the column "Wert" is not "SaaS". Therefore the rows with the values "Genutzte Technologien", "Hosting", "Datenbank-Server", "Datenbank", "Datenbank-Typ", "Datenbank-Version", "Applikationsserver" and "Betriebssystem" in the column "Feld" shall be used for the calculation. Because there are only 4 of them set and the rest is empty I expect the result to be 50,00%.

I tried both of your approaches, but in both ways for my first case I get 75,00% and for my second I get 50,00%. So it seems the calculation is not depending on the value "SaaS". Does this help to better understand my problem?