SUM(INDEX(1/COUNTIFS in Power BI

[Saxon10]

Hi,

How can I do sum and countifs in Power BI.

I have a following column are item, id type, colour code text in my Table and I would like to get the unique count based those columns.

I am able to count by using below mentioned DAX code but I don't how can I do sum and index in my exciting DAX code.

countifs =

VAR ITEEM = Data[Item]

VAR IDTYPE =Data[Id Type]

VAR CCTEXT = Data[Colour Code Text]

RETURN

COUNTROWS(FILTER(Data,

[Item]=ITEEM && [Id Type]=IDTYPE && [Colour Code Text]=CCTEXT

))

In Excel I am applying the following formula and I would like to get the same thing in PBI. Could you please provide the DAX solution.

D3=SUM(INDEX(1/COUNTIFS(A$3:A$101,A3,B$3:B$101,B3,C$3:C$101,C3),0))

Herewith attached the PBI for your reference.

https://www.dropbox.com/s/6viom43bcbfrklo/SUM%20INDEX%20COUNTIFS.pbix?dl=0

[CNENFRNL · Accepted solution]

@Saxon10 ,for your reference,

=  1 / COUNTROWS(
        FILTER(
            'Table',
            'Table'[Item] = EARLIER( 'Table'[Item] )
                && 'Table'[Id Type] = EARLIER( 'Table'[Id Type] )
                && 'Table'[Colour Code Text] = EARLIER( 'Table'[Colour Code Text] )
        )
    )

Another piece of advice on Excel formula, using Structured Reference makes formula readable and dynamic. Structured Reference is one of the most elegant features of Excel.

[Saxon10]

Thanks for your reply and help.

Your solution is working well. 

I try to make structured references formula so it's easy to read it. 

[amitchandak]

@Saxon10 ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Saxon10]

Thanks for your reply. Here is the data for your reference.

Herewith attached the PBI for your reference 

https://www.dropbox.com/s/6viom43bcbfrklo/SUM%20INDEX%20COUNTIFS.pbix?dl=0