Simple Having Clause created with Power BI measure

Question

I created this simple table in SQL:

create table testTable (
date date not null,
leader varchar(20),
name varchar(20)
)


insert into testTable 
values
('2021-01-01', 'KIM', 'Anders'),
('2021-01-02', 'KIM', 'Annika'),
('2021-01-03', 'KIM', 'Anna'),
('2021-01-04', 'KIM', 'Anna'),
('2021-01-03', 'KIM', 'Annika'),
('2021-01-01', 'JOHAN', 'Sara'),
('2021-01-02', 'JOHAN', 'Sara'),
('2021-01-03', 'JOHAN', 'Sara')

I am trying to get an ekvivalent solution to the following code in a dax measure if possible

select max(leader), name, count(name)
from testTable
group by name
having count(name) >= 2

The result that im looking for is.

Leader	Measure
KIM	2
JOHAN	1

user_7297700 · Answer

Think about HAVING as a filter that happens after a grouping. So something like

Measure = COUNTROWS(filter(SUMMARIZECOLUMNS('Table'[Name],"Count",count('Table'[Name])), [Count]>=2))

And here's a simple way to present test data for DAX questions, entirely in DAX:

testTable = SELECTCOLUMNS
(
    {
         (date(2021,01,01),"KIM","Anders")
        ,(date(2021,01,02),"KIM","Annika")
        ,(date(2021,01,03),"KIM","Anna")
        ,(date(2021,01,04),"KIM","Anna")
        ,(date(2021,01,03),"KIM","Annika")
        ,(date(2021,01,01),"JOHAN","Sara")
        ,(date(2021,01,02),"JOHAN","Sara")
        ,(date(2021,01,03),"JOHAN","Sara")
    }, "date",   [Value1]
     , "leader", [Value2]
     , "name",   [Value3]
)

This is much easier way to reproduce a scenario than creating a table in SQL Server, and loading it through Power Query, or using the "Enter Data" form in PowerBI which creates the table in Power Query.

user_248949 · Answer

Edit: after adding the desired result to the question, the answer changes like follows

A possible solution is to implement a measure that counts the number of names that appear more than once for the selected leader

# Names ge 2 =
COUNTROWS (
    FILTER (
        VALUES ( Test[name] ),
        CALCULATE ( COUNTROWS ( Test ), ALLEXCEPT ( Test, Test[name], Test[leader] ) ) > 1
    )
)

here is a working example on dax.do

DEFINE
    TABLE Test =
        DATATABLE (
            "date", DATETIME,
            "leader", STRING,
            "name", STRING,
            {
                { "2021-01-01", "KIM", "Anders" },
                { "2021-01-02", "KIM", "Annika" },
                { "2021-01-03", "KIM", "Anna" },
                { "2021-01-04", "KIM", "Anna" },
                { "2021-01-03", "KIM", "Annika" },
                { "2021-01-01", "JOHAN", "Sara" },
                { "2021-01-02", "JOHAN", "Sara" },
                { "2021-01-03", "JOHAN", "Sara" }
            }
        )
    MEASURE Test[# Names ge 2] =
        COUNTROWS (
            FILTER (
                VALUES ( Test[name] ),
                CALCULATE ( COUNTROWS ( Test ), ALLEXCEPT ( Test, Test[name], Test[leader] ) ) > 1
            )
        )
EVALUATE
SUMMARIZECOLUMNS (
    Test[leader],
    "# Names ge 2", [# Names ge 2]
)

and the resulting output

I've left the measure of my previous answer on the original dax.do, that returned this output

Simple Having Clause created with Power BI measure

2 Answers