DAX Function for Count with filter

[powerbirookiee]

Hi everyone,

I am new to Power BI and I have no idea in complex DAX functions. I need to create a calculated column or measure (whichever is more appropriate) which counts unique combinations of companies + tags but with some applied rules.

articleID	Company	Tag	Company + Tag	Count	Desired Count
123a	ABC	Black	ABC - Black	1	1
123a	ABC	Pink	ABC - Pink	2	-
456b	DEF	Black	DEF - Black	3	2
789c	XYZ	Pink	XYZ - Pink	4	3
111d	AAA	White	AAA - White	5	4
222e	BBB	Black	BBB - Black	6	5
222e	BBB	Pink	BBB - Pink	7	-
222e	BBB	White	BBB - White	8	6
				Count Distinct - 8	Desired Count - 6

In the sample table above, "PINK" should NOT be counted if and whenever it belongs to the same article as tag "BLACK". If they have the same article ID, BLACK should be the only one counted and Pink should be blank (articleIDs 123a and 222e). Pink can only be counted if it is a standalone tag (articleID 789c).

What is the DAX function for this scenario? Any help is appreciated. Thanks!

[Anonymous · Accepted solution]

Hi @powerbirookiee ,

 Greg_Deckler's measure will only return 1 in subtoatl. Please try this code to create a measure.

Desired Count = 
VAR __ADD =
    ADDCOLUMNS (
        'Table',
        "Count",
            VAR _COUNTBYID =
                CALCULATE ( COUNTROWS ( 'Table' ), ALLEXCEPT ( 'Table', 'Table'[articleID] ) )
            RETURN
                SWITCH ( TRUE (), [Tag] = "Pink" && _COUNTBYID > 1, BLANK (), 1 )
    )
RETURN
    SUMX ( __ADD, [Count] )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@powerbirookiee So try this:

Desired Count =
  VAR __article = MAX('Table'[articleID])
  VAR __tag = MAX('Table'[Tag])
  VAR __tags = COUNTROWS(FILTER(ALL('Table'),[articleID] = __article))
  VAR __Result = 
    SWITCH(TRUE(),
      __tag = "Pink" && __tags > 1,BLANK(),
      1
    )
RETURN
  __Result

Then you will need this:

First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8