Percentage of confirmations in in DirectQUERY

[Anonymous]

Hi All,

I have a certain case to solve that is easily solved in regular DAX, while it is highly problematic in DircetQUERY.

Having the following column consisting of "Y" and "N", answering the question of whether the process is correct - Y, or not correct - "N".

 The column above is linked through "SEQ" to the questions for which the answer is given. concerns.

Additionally, each question is confirmed by several shifts (4) throughout the day.

I would like to create a new measure/column that shows the percentage of positive confirmations to a given question. Over some time.

Explanation: In a week, a question was confirmed 12 times as positive and 8 times as negative. What I want to get is the % of positive confirmations of the process of a given question.

In DAX I have :
Test = CALCULATE( COUNTROWS(Test), FILTER(Test,Test[OKNOTOK]=EARLIER(Test[OKNOTOK])))

% = Test[Test]/COUNTROWS(Test)

Thank you for your help,
Lukasz

[amitchandak]

@Anonymous , In direct query you need to create it like measure

Test measure = CALCULATE( COUNTROWS(Test), FILTER(allselected(Test),Test[OKNOTOK]=max(Test[OKNOTOK])))

% = Divide(Sum(Test[Test]),[Test Measure])

[Anonymous]

Hi! Thank you for your answer 🙂 

Actually the "measure" function works when creating new measures.

However, I still have a problem. Maybe on actual data it will be easier for me to understand.

So: I'm based on 3 tables. Each connected to the other.

1. CONFIRMATIONOPERATION

2. CONFIRMATIONS

2. TBLSHIFT

I created 2 measuers:
1: Measure = CALCULATE(COUNTROWS(CONFIRMATIONS),FILTER(ALLSELECTED(CONFIRMATIONS),CONFIRMATIONS[OKNOTOK]=MAX(CONFIRMATIONS[OKNOTOK])))

2: % = [Measure]/COUNTROWS(CONFIRMATIONS)

Results:

Where's the problem? Thank you for your support

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ，

What's your expected result?  Is there any relationship created between tables CONFIRMATIONOPERATION,CONFIRMATIONS and TBLSHIFT? Which column is indicating the "question"? Are you trying to get a positive percentage for each question? For example, for question A, where the positive times is 12 and the negative times is 8, then the percentage of positive for question A is 12/(12+8)=0.6? I created a sample pbix file(see attachment), please check whether that is what you want...

1. Create sample tables

Sample dataSample data

2. Create relationships among tables

Relationships

3. Create a measure as below

% = 
VAR _positive =
    CALCULATE (
        COUNT ( 'CONFIRMATIONS'[OKNOTOK] ),
        FILTER (
            'CONFIRMATIONS',
            'CONFIRMATIONS'[SHIFTTEAMID] = SELECTEDVALUE ( 'TBLSHIFT'[ID] )
                && 'CONFIRMATIONS'[OKNOTOK] = "Y"
        )
    )
VAR _all =
    CALCULATE (
        COUNT ( 'CONFIRMATIONS'[OKNOTOK] ),
        FILTER (
            'CONFIRMATIONS',
            'CONFIRMATIONS'[SHIFTTEAMID] = SELECTEDVALUE ( 'TBLSHIFT'[ID] )
        )
    )
RETURN
    DIVIDE ( _positive, _all, 0 )

If the above measure is not working in your scenario, please share the required information(sample data with Text format, backend logic and expected result etc..) with me. Later I will provide you a suitable solution base on your provided information. Thank you.

Best Regards

[Anonymous]

Thank you very much for your help. The solution is correct. Thank you all for your involvement!