Forum Discussion

powerbirookiee's avatar
powerbirookiee
Frequent Visitor
3 years ago
Solved

DAX Function for Count with filter

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
3 years ago

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