Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only

View My Drafts

  • 1.  Using DISTINCT and FILTER Function

    Posted 15 days ago
    HI Everyone,

    I want to ask regarding using the DISTINCT and FILTER function, so I want to calculate the SUM for Rating column, based on the filter in the SubscriptionClassification, which contain two filter value COMPLIANT and NOT_APPLICABLE (the NOT_APPLICLABLE could not be seen in the picture here) .
    Then I need to SUM the based only distinct value in column Control ID, so basically only one row.

    I have tried multiple way but nothing works, I create two or three separate Measure, one for the FILTER and other for the DISTINCT, and other for the MAX value for Rating column(I got this suggestion from Youtube i found)

    The best solution I can currently find is here:
    1. I put firstly the Max RATING formula : Max control Rating = MAX( Table1[Rating] )
    2. then put the : Sum Distinct = SUMX(DISTINCT( Table1[Control ID] ), Table1 [Max control Rating] )
    3. then put : FILTER Control ID = SUMX( FILTER(Sheet1, OR( Table1[SubscriptionClassification] = "COMPLIANT" , Table1[SubscriptionScore] = "NOT_APPLICABLE") ), Sheet1[Sum Distinct] )

    but the third step give an error when I try to put in diagram or table visualization.

    However, funny things it works properly with AVERAGEX function
    with:
    1. AverageFilter = AVERAGEX ( FILTER( Sheet1 , OR (Table1[SubscriptionClassification] = "COMPlIANT" , Table1[SubscriptionClassification] = "NOT_APPLICABLE")) , Table1[Rating])
    2. then put : Distinct Average Control ID = AVERAGEX(DISTINCT( Table1 [Control ID]), Table1 [AverageFilter2])


    That is my case, if anyone have a solution and suggestion please let me know, thank you very much :)


    ------------------------------
    Iman Arrovi
    Intern/Trainee
    ------------------------------


  • 2.  RE: Using DISTINCT and FILTER Function

    Posted 15 days ago
    I just find the solutions myself after try many DAX

    so the first thing is to filter the DISTINCT with MAX filter:
    1. Max control Rating = MAX(Table1 [Rating])

    then put the KEEPFILTERS to search the validation in SubscribtionClassification column, and use CALCULATE and SUMX to make it works, as well the DISTICNT again
    2. Rating Distinct =
    CALCULATE(
        SUMX(DISTINCT(AZURE_Cloudinspector_assessment[Control ID]), AZURE_Cloudinspector_assessment[Max control Rating]),
        KEEPFILTERS( OR(AZURE_Cloudinspector_assessment[SubscriptionClassification] = "COMPLIANT" , AZURE_Cloudinspector_assessment[SubscriptionClassification]= "NOT_APPLICABLE")
    ))

    I hope it will be useful for someone working with Filter and Distinct 👍


    ------------------------------
    Iman Arrovi
    Intern/Trainee
    ------------------------------

    Original Message